修正SQL查询：将员工薪资多行结果合并为单行输出

现有SQL查询语句如下：

SELECT unf_employee.employee_code,unf_employee.name AS Employee_Name, unf_attendances.payable_amount, unf_processed_salary_heads.type, unf_processed_salary_heads.name AS title, unf_processed_salary_heads.amount 
FROM unf_attendances 
INNER JOIN unf_attendances_unf_processed_salary_heads_c ON UNF_Attendances.id = unf_attendances_unf_processed_salary_heads_c.unf_attendances_unf_processed_salary_headsunf_attendances_ida 
INNER JOIN unf_processed_salary_heads ON unf_attendances_unf_processed_salary_heads_c.unf_attend5f51y_heads_idb = unf_processed_salary_heads.id 
INNER JOIN unf_employee ON unf_attendances.unf_employee_id_c = unf_employee.id 
WHERE unf_attendances.deleted = 0

当前该查询返回6行结果，期望将结果合并为单行，输出样式匹配指定的预期表格，请修正该SQL查询以实现需求。

根据需求，你可以通过行转列的方式将多行结果合并为单行，以下提供两种常用实现方式：

方式1：固定薪资项转独立列（匹配预期表格样式） #

适合已知所有需要展示的薪资项名称的场景：

SELECT 
    unf_employee.employee_code,
    unf_employee.name AS Employee_Name,
    unf_attendances.payable_amount,
    -- 替换为你实际的薪资项title值，按需添加更多列
    SUM(CASE WHEN unf_processed_salary_heads.name = '基本工资' THEN unf_processed_salary_heads.amount ELSE 0 END) AS 基本工资,
    SUM(CASE WHEN unf_processed_salary_heads.name = '交通补贴' THEN unf_processed_salary_heads.amount ELSE 0 END) AS 交通补贴,
    SUM(CASE WHEN unf_processed_salary_heads.name = '住房补贴' THEN unf_processed_salary_heads.amount ELSE 0 END) AS 住房补贴
FROM unf_attendances 
INNER JOIN unf_attendances_unf_processed_salary_heads_c ON UNF_Attendances.id = unf_attendances_unf_processed_salary_heads_c.unf_attendances_unf_processed_salary_headsunf_attendances_ida 
INNER JOIN unf_processed_salary_heads ON unf_attendances_unf_processed_salary_heads_c.unf_attend5f51y_heads_idb = unf_processed_salary_heads.id 
INNER JOIN unf_employee ON unf_attendances.unf_employee_id_c = unf_employee.id 
WHERE unf_attendances.deleted = 0
GROUP BY unf_employee.employee_code, unf_employee.name, unf_attendances.payable_amount;

方式2：动态拼接所有薪资项（灵活适配未知项） #

如果不确定所有薪资项名称，可将所有项拼接为一个字段：

SELECT 
    unf_employee.employee_code,
    unf_employee.name AS Employee_Name,
    unf_attendances.payable_amount,
    GROUP_CONCAT(CONCAT(unf_processed_salary_heads.name, ':', unf_processed_salary_heads.amount) SEPARATOR '; ') AS 所有薪资明细
FROM unf_attendances 
INNER JOIN unf_attendances_unf_processed_salary_heads_c ON UNF_Attendances.id = unf_attendances_unf_processed_salary_heads_c.unf_attendances_unf_processed_salary_headsunf_attendances_ida 
INNER JOIN unf_processed_salary_heads ON unf_attendances_unf_processed_salary_heads_c.unf_attend5f51y_heads_idb = unf_processed_salary_heads.id 
INNER JOIN unf_employee ON unf_attendances.unf_employee_id_c = unf_employee.id 
WHERE unf_attendances.deleted = 0
GROUP BY unf_employee.employee_code, unf_employee.name, unf_attendances.payable_amount;

关键说明 #

• 两种方式都通过GROUP BY按员工唯一标识（employee_code）、姓名和应发金额分组，确保每个员工仅返回一行结果。
• 方式1需要根据实际的title值调整CASE WHEN条件，能精确匹配你要的表格样式；方式2无需提前知道所有薪资项，适合灵活展示场景。

内容的提问来源于stack exchange，提问作者Henil Code