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