关于数据库表的列转行，合并多行数据的问题解决

淡淡的烟草味﹌ 2022-10-23 15:26 93阅读 0赞

## 问题描述 ##

原始SQL是这样的：

SELECT
    	i.f_info_id id,
    	de.f_title title,
    	de.f_meta_description decs,
    	cu.f_key,
    	cu.f_value 
    FROM
    	cms_info_detail de
    	INNER JOIN cms_info i ON de.f_info_id = i.f_info_id
    	INNER JOIN cms_node node ON node.f_node_id = i.f_node_id
    	INNER JOIN cms_info_custom cu ON cu.f_info_id = i.f_info_id 
    WHERE
    	node.f_node_id = 69 
    	OR node.f_parent_id = 69

得到的数据是：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnh1bmZz_size_16_color_FFFFFF_t_70]

此时的数据是很难在前端展示的，需要处理一下数据

## 处理数据 ##

先是 **列转行** ，就后面两列的数据处理一下  
具体SQL是:

SELECT
    		i.f_info_id id,
    		de.f_title title,
    		de.f_meta_description decs,
    		( CASE cu.f_key WHEN "dept" THEN f_value ELSE NULL END ) AS dept,
    		( CASE cu.f_key WHEN "location" THEN f_value ELSE NULL END ) AS location,
    		( CASE cu.f_key WHEN "jobtype" THEN f_value ELSE NULL END ) AS jobtype,
    		( CASE cu.f_key WHEN "responsibilities" THEN f_value ELSE NULL END ) AS responsibilities,
    		( CASE cu.f_key WHEN "qualifications" THEN f_value ELSE NULL END ) AS qualifications,
    		( CASE cu.f_key WHEN "type" THEN f_value ELSE NULL END ) AS type 
    	FROM
    		cms_info_detail de
    		INNER JOIN cms_info i ON de.f_info_id = i.f_info_id
    		INNER JOIN cms_node node ON node.f_node_id = i.f_node_id
    		INNER JOIN cms_info_custom cu ON cu.f_info_id = i.f_info_id WHERE node.f_node_id = 69 
    		OR node.f_parent_id = 69

输出：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnh1bmZz_size_16_color_FFFFFF_t_70 1]

## 合并多行数据 ##

需要用到的函数：  
***GROUP\_CONCAT(m.modelName SEPARATOR ‘,’) modelName***

1.GROUP\_CONCAT（）中的值为你要合并的数据的字段名;

SEPARATOR 函数是用来分隔这些要合并的数据的；

’ '中是你要用哪个符号来分隔；

2.必须要用**GROUP BY** 语句来进行分组管理，不然所有的数据都会被合并成一条记录，如图：  
![在这里插入图片描述][20210307150156934.png]  
示例：

输出：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnh1bmZz_size_16_color_FFFFFF_t_70 2]

完整SQL展示:

SELECT
    	datas.id id,
    	datas.title title,
    	datas.decs description,
    	GROUP_CONCAT( datas.location SEPARATOR '' ) 地点,
    	GROUP_CONCAT( datas.jobtype SEPARATOR '' ) 工作类型,
    	GROUP_CONCAT( datas.responsibilities SEPARATOR '' ) 职责,
    	GROUP_CONCAT( datas.qualifications SEPARATOR '' ) 要求,
    	GROUP_CONCAT( datas.type SEPARATOR '' ) 招收来源,
    	GROUP_CONCAT( datas.number SEPARATOR '' ) 数量,
    	GROUP_CONCAT( datas.education SEPARATOR '' ) 受教育程度,
    	GROUP_CONCAT( datas.experience SEPARATOR '' ) 经历
    FROM
    	(
    	SELECT
    		i.f_info_id id,
    		de.f_title title,
    		de.f_meta_description decs,
    		( CASE cu.f_key WHEN "location" THEN f_value ELSE NULL END ) AS location,
    		( CASE cu.f_key WHEN "jobtype" THEN f_value ELSE NULL END ) AS jobtype,
    		( CASE cu.f_key WHEN "responsibilities" THEN f_value ELSE NULL END ) AS responsibilities,
    		( CASE cu.f_key WHEN "qualifications" THEN f_value ELSE NULL END ) AS qualifications,
    		( CASE cu.f_key WHEN "type" THEN f_value ELSE NULL END ) AS type,
    		( CASE cu.f_key WHEN "number" THEN f_value ELSE NULL END ) AS number,
    		( CASE cu.f_key WHEN "education" THEN f_value ELSE NULL END ) AS education,
    		( CASE cu.f_key WHEN "experience" THEN f_value ELSE NULL END ) AS experience
    	FROM
    		cms_info_detail de
    		INNER JOIN cms_info i ON de.f_info_id = i.f_info_id
    		INNER JOIN cms_node node ON node.f_node_id = i.f_node_id
    		INNER JOIN cms_info_custom cu ON cu.f_info_id = i.f_info_id 
    	WHERE
    		node.f_node_id = 69 
    		OR node.f_parent_id = 69 
    	) datas 
    GROUP BY
    	id

参考：  
[SQL 列转行][SQL]  
[mysql中将多行数据合并成一行数据][mysql]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnh1bmZz_size_16_color_FFFFFF_t_70]: /images/20221023/ff9efcf4a96d4d7bab3409f4a55f8239.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnh1bmZz_size_16_color_FFFFFF_t_70 1]: /images/20221023/b2dac1b955d9478eafd28f246805fbbc.png
[20210307150156934.png]: /images/20221023/58f025a8b8d248d794a43b9fdef1a06a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnh1bmZz_size_16_color_FFFFFF_t_70 2]: /images/20221023/30255cee930c4952879b9ef3f2804a8c.png
[SQL]: https://blog.csdn.net/vipxiaotian/article/details/4409423
[mysql]: https://www.cnblogs.com/guaiguaipaizz/p/6501676.html