sql:
select base_all.*,
case
when base_all.x_builder_salary_code in ('P002','P003','P006','P007','P008','P009','P005','P004') then base_all.P018+base_all.P002+base_all.P004+base_all.P003+base_all.P017
when base_all.x_builder_salary_code='P004' then base_all.P018 + base_all.P018/21.75*2*4+base_all.P005 + base_all.P004+base_all.P003
when base_all.x_builder_salary_code='P005' then base_all.P001*2 + base_all.P003
else 0
end salary_standard – 薪资标准
from
(select
emp.name emp_name,-- 员工姓名
emp.employee_number the_employee_number,-- 员工工号
hr_department.name department_name, – 部门名称
emp_job.x_businesstitle x_businesstitle,-- 业务头衔
builder_salary.x_code x_builder_salary_code,
sum(case when parchc.code='P001' then parch.amount else 0 end) P001,
sum(case when parchc.code='P002' then parch.amount else 0 end) P002,
sum(case when parchc.code='P003' then parch.amount else 0 end) P003,
sum(case when parchc.code='P004' then parch.amount else 0 end) P004,
sum(case when parchc.code='P005' then parch.amount else 0 end) P005,
sum(case when parchc.code='P006' then parch.amount else 0 end) P006,
sum(case when parchc.code='P007' then parch.amount else 0 end) P007,
sum(case when parchc.code='P008' then parch.amount else 0 end) P008,
sum(case when parchc.code='P009' then parch.amount else 0 end) P009,
sum(case when parchc.code='P010' then parch.amount else 0 end) P010,
sum(case when parchc.code='P011' then parch.amount else 0 end) P011,
sum(case when parchc.code='P012' then parch.amount else 0 end) P012,
sum(case when parchc.code='P013' then parch.amount else 0 end) P013,
sum(case when parchc.code='P013' then parch.amount else 0 end) P014,
sum(case when parchc.code='P015' then parch.amount else 0 end) P015,
sum(case when parchc.code='P016' then parch.amount else 0 end) P016,
sum(case when parchc.code='P017' then parch.amount else 0 end) P017,
sum(case when parchc.code='P018' then parch.amount else 0 end) P018,
sum(case when parchc.code='P019' then parch.amount else 0 end) P019,
sum(case when parchc.code='P020' then parch.amount else 0 end) P020,
sum(case when parchc.code='P021' then parch.amount else 0 end) P021,
sum(case when parchc.code='P022' then parch.amount else 0 end) P022
from
hr_employee emp
left join payroll_archive parch on parch.employee_id=emp.id
and parch.effective_date <= current_date AND
(parch.expired_date >= current_date OR parch.expired_date IS NULL) AND
parch.active = True
left join payroll_archive_config parchc on parch.payroll_item_id=parchc.id
inner join emp_job on emp.id = emp_job.employee_id and
emp_job.eroad_index_id IS NOT NULL and
emp_job.eroad_start_date <= current_date AND
(emp_job.eroad_end_date >= current_date OR emp_job.eroad_end_date IS NULL) AND
emp_job.status = 'active' AND
emp_job.active = TRUE AND
emp_job.employee_id IS NOT NULL
left join hr_department on emp_job.department_id = hr_department.id
left join x_builder_salary_scheme builder_salary on emp_job.x_xzgz=builder_salary.id
where emp.active=True
group by emp_name,the_employee_number,department_name,x_businesstitle,x_builder_salary_code) base_all
High 
杨光磊
张治辉