Mysql group_concat函数列转行,与行转列
例一:
SELECT num from user
1、使用group_concat函数得到列转行
select group_concat(num) from user
2、使用SUBSTRING_INDEX和CROSS JOIN将列里面的的数字都拆分出来,把一行变成一列
方法一:建配置表(网上查询的方法):
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 );
配置表sequence的结果为0-99的一列数字:
SQL:
SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(num, ',', seq), ',' ,- 1 ) num FROM sequence CROSS JOIN user WHERE seq BETWEEN 1 AND ( SELECT 1 + LENGTH(num) - LENGTH( REPLACE (num, ',', '') ) )
方法二:将sequence替换为SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user LIMIT 0,100) ,其中user为表名,这张表需要大于100条。(自己不想建表,图省事)
SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(num, ',', seq), ',' ,- 1 ) num FROM (SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user LIMIT 0,100) b CROSS JOIN user WHERE seq BETWEEN 1 AND ( SELECT 1 + LENGTH(num) - LENGTH(REPLACE(num, ',', '')))
两种方法结果均为:
例二:
将这样的数据拆分出来,变成如下:
还是使用上述sql,只需添加一列即可。(标记红色为新添加)
SELECT ID,SUBSTRING_INDEX( SUBSTRING_INDEX(num, ',', seq), ',' ,- 1 ) num FROM (SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user LIMIT 0,100) b CROSS JOIN user WHERE seq BETWEEN 1 AND ( SELECT 1 + LENGTH(num) - LENGTH(REPLACE(num, ',', '')))
4、补充
1) 本篇文章中,user为表名,num为字段,自行替换就好。
相关文章:Mysql语句优化