问题描述
原始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