mysql 列转行

一、列转行

mysql 数据库中，我们可能遇到将数据库中某一列的数据（多个值，按照英文逗号分隔），转化为多行数据（即一行转多行），然后join关联表，再转化为一行数据

如：有两张表，一用户表，一张学科表，需要查询学科表中的用户姓名

用户表

id	username	age
1	zhangsan	20
2	lisi	21
3	wamhwu	22

学科表

id	user_ids	subject
1	1,2,3	数学
2	2,3	语文
3	1,2	英语

我们首先需要把学科表中的user_ids拆分成多行

id	user_id	subject
1	1	数学
1	2	数学
1	3	数学
2	2	语文
2	3	语文
3	1	英语
3	2	英语

二、普通的实现方式(需要依赖 mysql.help_topic 表)

SELECT
    a.id,
    a.subject,
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.`user_ids`, ',', b.help_topic_id + 1 ), ',',-1 ) user_id
FROM
    test a
    JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`user_ids`) - LENGTH( REPLACE ( a.`user_ids`, ',', '' ) ) + 1 );

三、mysql.help_topic 无权限处理办法

mysql.help_topic 的作用是对 SUBSTRING_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。

如果 mysql.help_topic 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。

获取该字段最多可以分割成为几个字符串:
 

SELECT MAX(LENGTH(a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '' )) + 1) FROM `test` a;

创建临时表，并给临时表添加数据:

注意:

1. 临时表必须有一列从 0 或者 1 开始的自增数据
2. 临时表表名随意，字段可以只有一个
3. 临时表示的数据量必须比 MAX(LENGTH(a.user_ids) - LENGTH(REPLACE(a.user_ids, ',', '' )) + 1) 的值大

DROP TABLE IF EXISTS `tmp_help_topic`;
CREATE TABLE IF NOT EXISTS `tmp_help_topic` (
  `help_topic_id` bigint(20) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`help_topic_id`)
);
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();

四、查询函数

SELECT
    a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 );

五、join用户表，关联用户名

select 
t2.*,
u.username
from ( 
  SELECT
    a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 ) 
) t2 join user u 
on u.id = t2.user_id

id	user_id	subject	username
1	1	数学	zhangsan
1	2	数学	lisi
1	3	数学	wangwu
2	2	语文	lisi
2	3	语文	wangwu
3	1	英语	zhangsan
3	2	英语	lisi

六、将多行数据转化为一行

select 
t2.*,
group_concat(u.username) username
from ( 
  SELECT
    a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 ) 
) t2 join user u 
on u.id = t2.user_id
group by t2.id

id	subject	user_ids	username
1	数学	1,2,3	zhangsan,lisi,wangwu
2	语文	2,3	lisi,wangwu
3	英语	1,2	zhangsan,lisi

说明：

SUBSTRING_INDEX(SUBSTRING_INDEX(a.user_ids, ',', b.help_topic_id), ',',-1 ) 就是获取 tmp_help_topic 表的 help_topic_id 字段的值作为 name 字段的第几个子串
使用了 join 就会把字段 user_ids 分为 (LENGTH( a.user_ids) - LENGTH(REPLACE(a.user_ids, ',', '')) + 1 ) 行，并且每行的字段刚好是 user_ids字段的第 help_topic_id 个子串

GROUP_CONCAT函数用于将GROUP BY产生的同一个分组中的值连接起来，返回一个字符串结果

GROUP_CONCAT函数首先根据GROUP BY指定的列进行分组，将同一组的列显示出来，并且用分隔符分隔，由函数参数(字段名)决定要返回的列

语法结构

GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

说明:

(1) 使用DISTINCT可以排除重复值

(2) 如果需要对结果中的值进行排序，可以使用ORDER BY子句

(3) SEPARATOR '分隔符'是一个字符串值，默认为逗号