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

问题描述

原始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

得到的数据是：

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

处理数据

先是 列转行 ，就后面两列的数据处理一下
具体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 

输出：

合并多行数据

需要用到的函数：
GROUP_CONCAT(m.modelName SEPARATOR ‘,’) modelName

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

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

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

2.必须要用GROUP BY 语句来进行分组管理，不然所有的数据都会被合并成一条记录，如图：

示例：

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
	(
	子查询（上面的SQL）
	) datas 
GROUP BY
	id

输出：

完整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 列转行
mysql中将多行数据合并成一行数据