用过Mysql的都知道她有一个很好的实现行转列功能的函数group_concat函数,非常方便
-
SELECT
-
*
-
FROM
-
group_test;
-
-
SELECT
-
id,
-
GROUP_CONCAT(sub_id)
-
FROM
-
`group_test`
-
GROUP BY
-
id;
现在的需求是有上面图二类似的结果集,需要把列二拆分 转换成行记录
我们知道如果是单条记录通过SUBSTRING_INDEX容易实现
-
select id,SUBSTRING_INDEX(sub_id,',',1) from group_test where id=3
-
UNION
-
select id,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_id,',',2),',',-1) from group_test where id=3
-
UNION
-
select id,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_id,',',3),',',-1) from group_test where id=3
但是如果是N条呢?同样也是可以使用SUBSTRING_INDEX来实现,只不过需要一个配置表,通过CROSS JOIN交叉连接实现,先看下CROSS JOIN
-
SELECT
-
*
-
FROM
-
(SELECT 1 UNION SELECT 2) t1
-
CROSS JOIN (SELECT 3 UNION SELECT 4) t2
下面就通过CROSS JOIN和SUBSTRING_INDEX实现我们的需求,首先构建一个配置表
-
CREATE TABLE digits (digit INT(1));
-
INSERT INTO digits
-
VALUES
-
(0),
-
(1),
-
(2),
-
(3),
-
(4),
-
(5),
-
(6),
-
(7),
-
(8),
-
(9);
-
CREATE TABLE sequence (seq INT(3));
-
INSERT INTO sequence (
-
SELECT
-
D1.digit + D2.digit * 10
-
FROM
-
digits D1
-
CROSS JOIN digits D2
-
);
然后
-
SELECT
-
id,
-
SUBSTRING_INDEX(
-
SUBSTRING_INDEX(sub_id, ',', seq),
-
',' ,- 1
-
) sub_id,
-
seq
-
FROM
-
sequence
-
CROSS JOIN group_test
-
WHERE
-
seq BETWEEN 1
-
AND (
-
SELECT
-
1 + LENGTH(sub_id) - LENGTH(REPLACE(sub_id, ',', ''))
-
)
-
ORDER BY
-
id,
-
sub_id;
然后就没有然后了。如图上的代码 有几个地方使用还是很巧妙的 不是吗?
最后此方法是不是比写个存储过程或者PHP/PYTHON简单些呢^_^
阅读(42186) | 评论(0) | 转发(1) |