sql如下即可，员工花名册的另外字段可以通过第一个员工字段级联取得： select emp.id x_employee, sum(case when payroll_archive.code='contract_bs' then payroll_archive.amount else 0 end) as x_contract_bs, sum(case when payroll_archive.code='F008' then payroll_archive.amount else 0 end) as x_F008, sum(case when payroll_archive.code='F009' then payroll_archive.amount else 0 end) as x_F009, sum(case when payroll_archive.code='A002' then payroll_archive.amount else 0 end) as x_A002, sum(case when payroll_archive.code='F003' then payroll_archive.amount else 0 end) as x_F003, sum(case when payroll_archive.code='F004' then payroll_archive.amount else 0 end) as x_F004, sum(case when payroll_archive.code='F002' then payroll_archive.amount else 0 end) as x_F002, sum(case when payroll_archive.code='F006' then payroll_archive.amount else 0 end) as x_F006, sum(case when payroll_archive.code='F007' then payroll_archive.amount else 0 end) as x_F007, sum(case when payroll_archive.code='F005' then payroll_archive.amount else 0 end) as x_F005 from hr_employee emp left join payroll_archive on payroll_archive.employee_id=emp.id and payroll_archive.effective_date<=CURRENT_DATE and (payroll_archive.expired_date>=CURRENT_DATE or payroll_archive.expired_date is null) where emp.work_activity='in_service' group by emp.id