用过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;
原贴: http://blog.chinaunix.net/uid-411974-id-3990697.html
( 转 ) Mysql group_concat 的反向应用实现（Mysql列转行）
原文：https://www.cnblogs.com/refuge/p/9168906.html
