sql: select hr_employee.employee_number 工号, hr_employee.name 员工姓名, coalesce(adjustment_type_trans.value,adjustment_type.name) 调整类型, coalesce(adjustment_reason_trans.value,adjustment_reason.name) 调整原因, salary_adjustment.effective_date 生效日期, sum(case when compensation_adjustment_line.archive_config_rel_code='PA004' then compensation_adjustment_line.before_adjustment else 0 end) 调整前BasicSalary, sum(case when compensation_adjustment_line.archive_config_rel_code='PA006' then compensation_adjustment_line.before_adjustment else 0 end) 调整前GradeAllowance, sum(case when compensation_adjustment_line.archive_config_rel_code='PA007' then compensation_adjustment_line.before_adjustment else 0 end) 调整前PositionAllowance, sum(case when compensation_adjustment_line.archive_config_rel_code='PA008' then compensation_adjustment_line.before_adjustment else 0 end) 调整前DispatchAllowance, sum(case when compensation_adjustment_line.archive_config_rel_code='PA004' then compensation_adjustment_line.after_amount else 0 end) 调整后BasicSalary, sum(case when compensation_adjustment_line.archive_config_rel_code='PA006' then compensation_adjustment_line.after_amount else 0 end) 调整后GradeAllowance, sum(case when compensation_adjustment_line.archive_config_rel_code='PA007' then compensation_adjustment_line.after_amount else 0 end) 调整后PositionAllowance, sum(case when compensation_adjustment_line.archive_config_rel_code='PA008' then compensation_adjustment_line.after_amount else 0 end) 调整后DispatchAllowance from salary_adjustment left join hr_employee on salary_adjustment.employee_id=hr_employee.id left join adjustment_type on salary_adjustment.adjustment_type= adjustment_type.id left join ir_translation adjustment_type_trans on adjustment_type_trans.res_id=adjustment_type.id and adjustment_type_trans.name ='adjustment.type,name' and adjustment_type_trans.lang='zh_CN' left join adjustment_reason on salary_adjustment.adjustment_reason=adjustment_reason.id left join ir_translation adjustment_reason_trans on adjustment_type_trans.res_id=adjustment_reason.id and adjustment_reason_trans.name ='adjustment.reason,name' and adjustment_reason_trans.lang='zh_CN' left join compensation_adjustment_line on compensation_adjustment_line.job_adjustment_id=salary_adjustment.id where salary_adjustment.x_bp_state='done' and salary_adjustment.active='True' group by hr_employee.employee_number, hr_employee.name, coalesce(adjustment_type_trans.value,adjustment_type.name), coalesce(adjustment_reason_trans.value,adjustment_reason.name), salary_adjustment.effective_date