SQL查询需求：统计员工近1/3/6/12个月薪资总和

修正后的完整SQL代码 #

WITH t1 AS (
    SELECT 
        e.first_name, 
        e.last_name, 
        e.ID, 
        s.SALARY, 
        s.DATE
    FROM employee e
    JOIN department d ON d.emp_id = e.emp_id
    JOIN salary s ON s.dep_id = d.dep_id
)
SELECT 
    first_name,
    last_name,
    ID,
    SUM(CASE WHEN DATE >= CURRENT_DATE - INTERVAL '1 month' THEN SALARY ELSE 0 END) AS 1_month_salary,
    SUM(CASE WHEN DATE >= CURRENT_DATE - INTERVAL '3 months' THEN SALARY ELSE 0 END) AS 3_month_salary,
    SUM(CASE WHEN DATE >= CURRENT_DATE - INTERVAL '6 months' THEN SALARY ELSE 0 END) AS 6_month_salary,
    SUM(CASE WHEN DATE >= CURRENT_DATE - INTERVAL '1 year' THEN SALARY ELSE 0 END) AS 1_year_salary
FROM t1
GROUP BY first_name, last_name, ID;

关键修改说明 #

• 精简CTE字段：只保留后续计算必需的字段，避免冗余数据拖慢查询效率
• 条件聚合计算薪资：通过SUM(CASE ... END)实现按日期范围的薪资汇总，仅将落在目标时间段内的薪资记录计入对应总和
• 分组确保唯一行：按员工的姓名和ID分组，保证每个员工仅返回一条汇总结果
• 数据库日期语法兼容提示：不同数据库的日期运算语法有差异：
  • MySQL/MariaDB：将INTERVAL 'X month'替换为INTERVAL X MONTH，例如CURRENT_DATE - INTERVAL 1 MONTH
  • SQL Server：使用DATEADD(month, -1, GETDATE())替代CURRENT_DATE - INTERVAL '1 month'

内容的提问来源于stack exchange，提问作者user21575280