Access 2016 SQL查询：按年度获取各年度最高消费部门

问题分析 #

原SQL仅能查询单一特定年度的结果，要获取所有年度对应最高消费部门的记录，需要先完成部门年度消费汇总，再匹配每个年度的最高消费额，最终关联得到目标数据。

完整SQL语句 #

SELECT 
    d.年度,
    d.Department,
    d.SumOfActualCosts
FROM 
    (SELECT 
        Year(t.StartDate) AS 年度,
        e.Department,
        Sum(ac.Amount) AS SumOfActualCosts
    FROM 
        (Employee e
        INNER JOIN Trip t ON e.EmpID = t.EmpID)
        INNER JOIN ActualCosts ac ON t.TripID = ac.TripID
    GROUP BY 
        Year(t.StartDate), e.Department) AS d
INNER JOIN 
    (SELECT 
        年度,
        Max(SumOfActualCosts) AS MaxAnnualCost
    FROM 
        (SELECT 
            Year(t.StartDate) AS 年度,
            e.Department,
            Sum(ac.Amount) AS SumOfActualCosts
        FROM 
            (Employee e
            INNER JOIN Trip t ON e.EmpID = t.EmpID)
            INNER JOIN ActualCosts ac ON t.TripID = ac.TripID
        GROUP BY 
            Year(t.StartDate), e.Department) AS DeptYearCost
    GROUP BY 年度) AS m
ON 
    d.年度 = m.年度 
    AND d.SumOfActualCosts = m.MaxAnnualCost;

语句说明 #

1. 内层子查询（部门年度消费汇总）：

   • 关联Employee、Trip、ActualCosts三张表，通过EmpID和TripID建立关联。
   • 使用Year([StartDate])提取年度，按年度+部门分组，计算每个部门每年的总消费SumOfActualCosts。

2. 中间子查询（年度最高消费额）：

   • 基于部门年度消费汇总结果，按年度分组，提取每个年度的最高消费额MaxAnnualCost。

3. 外层关联查询：

   • 将部门年度消费数据与年度最高消费数据通过年度和消费额关联，筛选出每个年度消费总额最高的部门记录。

注：如果同一年度存在多个部门消费额并列最高，该语句会显示所有符合条件的部门记录。

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