记一次mysql查询问题，用group by和case when实现多行数据合并到一行

最终的查询语句是这样的：

SELECT source_code,source_name,
MAX(CASE plan_id WHEN '1001' THEN is_clean ELSE NULL END) is_clean,
MAX(CASE plan_id WHEN '1001' THEN unit_cost ELSE NULL END) unit_cost,
MAX(CASE plan_id WHEN '1001' THEN capa_scale ELSE NULL END) capa_scale,
MAX(CASE plan_id WHEN '1001' THEN unit_opex ELSE NULL END) unit_opex,
MAX(CASE plan_id WHEN '1002' THEN is_clean ELSE NULL END) is_clean,
MAX(CASE plan_id WHEN '1002' THEN unit_cost ELSE NULL END) unit_cost,
MAX(CASE plan_id WHEN '1002' THEN capa_scale ELSE NULL END) capa_scale,
MAX(CASE plan_id WHEN '1002' THEN unit_opex ELSE NULL END) unit_opex,
MAX(CASE plan_id WHEN '1003' THEN is_clean ELSE NULL END) is_clean,
MAX(CASE plan_id WHEN '1003' THEN unit_cost ELSE NULL END) unit_cost,
MAX(CASE plan_id WHEN '1003' THEN capa_scale ELSE NULL END) capa_scale,
MAX(CASE plan_id WHEN '1003' THEN unit_opex ELSE NULL END) unit_opex,
MAX(CASE plan_id WHEN '1004' THEN is_clean ELSE NULL END) is_clean,
MAX(CASE plan_id WHEN '1004' THEN unit_cost ELSE NULL END) unit_cost,
MAX(CASE plan_id WHEN '1004' THEN capa_scale ELSE NULL END) capa_scale,
MAX(CASE plan_id WHEN '1004' THEN unit_opex ELSE NULL END) unit_opex
FROM gpe_source_type D LEFT JOIN
(SELECT A.plan_id,A.parent_pro_id,C.source_type,A.plan_name,is_clean,unit_cost,capa_scale,unit_opex
FROM gpe_pro_plan_info A,(SELECT * FROM GPE_PRO_LCOE WHERE PARENT_PRO_ID = '1281') AS C
WHERE A.parent_pro_id=C.parent_pro_id AND A.plan_id=C.plan_id
)AS B 
ON D.source_code=B.source_type 
GROUP BY source_code;
           

其中表D是这样的

表B即

SELECT A.plan_id,A.parent_pro_id,C.source_type,A.plan_name,is_clean,unit_cost,capa_scale,unit_opex
FROM gpe_pro_plan_info A,(SELECT * FROM GPE_PRO_LCOE WHERE PARENT_PRO_ID = '1281') AS C
WHERE A.parent_pro_id=C.parent_pro_id AND A.plan_id=C.plan_id

           

这一大串的数据

是这样：

最后查询结果的表：