想要在SQL选择查询中显示员工的ID，最近3个月的工资总和，最近6个月的工资总和，1年工资的总和

Question

我想在select语句中显示员工姓名，ID，最近1个月的工资总额，最近3个月的工资总额，最近6个月的工资总额，最近1年的工资总额。员工每天获得工资，因此sum(salary)函数获得总金额。表:Employee, department and Salary(包含Salary和DATE列)

with t1 as (
select * from employee A
join department B ON B.emp_id=A.emp_id
join salary C on C.dep_id=B.dep_id
)
select first_name, last_name, ID,
'' as 1_month_salary,
'' as 3_month_salary,
'' as 6_month_salary,
'' as 1_year_salary
from t1;

我需要日期逻辑来添加基于日期和按列分组的值。

Answer 1

您可能希望使用case表达式有条件地聚合:

SELECT first_name, last_name, ID, 
SUM(CASE WHEN whatever_date_column BETWEEN add_months(sysdate,-1) AND sysdate THEN salary_column) END as 1_month_salary, 
SUM(CASE WHEN whatever_date_column BETWEEN add_months(sysdate,-3) AND sysdate THEN salary_column) END as 3_month_salary,
SUM(CASE WHEN whatever_date_column BETWEEN add_months(sysdate,-6) AND sysdate THEN salary_column) END as 6_month_salary
FROM t1
GROUP BY first_name, last_name, ID