SQL多表查询：筛选消费总额高于对应地点月均支出的人员

• 分组维度不符合需求：需求1要求按E_Date月份、L_Name分组，你当前GROUP BY子句未加入L_Name字段，完全不符合要求的统计维度
• 非聚合字段与GROUP BY不匹配：SQL规范要求SELECT子句中所有未被聚合函数包裹的字段，必须全部出现在GROUP BY子句中，你当前SELECT了R.R_Id、R.R_Name，部分数据库不允许仅在GROUP BY中写R_Id，会直接抛出语法错误
• 缺失要求输出的字段：需求1要求列出L_Name，你的SELECT子句没有包含该字段

需求1实现 #

逻辑：按月份、地点（L_Name）、人员（R_Name）三个维度分组（同一月份同一地点下可能存在多个消费人员，因此必须加入人员维度才能正确列出对应的R_Name），统计每个人员每月在对应地点的消费总金额

SELECT
    MONTH(E.E_Date) AS 消费月份,
    L.L_Name AS 地点名称,
    R.R_Id AS 人员ID,
    R.R_Name AS 人员姓名,
    SUM(E.E_Amt) AS 个人消费总金额
FROM L
INNER JOIN E ON L.L_Id = E.L_Id
INNER JOIN R ON R.R_Id = E.R_Id
GROUP BY
    MONTH(E.E_Date),
    L.L_Name,
    R.R_Id,
    R.R_Name;

需求2实现 #

逻辑：先通过窗口函数计算每个地点每月的人均消费作为平均支出，再筛选出个人消费总金额大于等于该值的记录即可

WITH monthly_stats AS (
    -- 先计算每个人员每月在各地点的消费总金额，同时计算对应地点当月的人均支出
    SELECT
        MONTH(E.E_Date) AS 消费月份,
        L.L_Name AS 地点名称,
        R.R_Id AS 人员ID,
        R.R_Name AS 人员姓名,
        SUM(E.E_Amt) AS 个人消费总金额,
        -- 计算当前地点当月的总消费 / 当月消费的不同人数，得到人均支出
        SUM(SUM(E.E_Amt)) OVER (PARTITION BY MONTH(E.E_Date), L.L_Name) 
        / COUNT(DISTINCT R.R_Id) OVER (PARTITION BY MONTH(E.E_Date), L.L_Name) AS 地点当月人均支出
    FROM L
    INNER JOIN E ON L.L_Id = E.L_Id
    INNER JOIN R ON R.R_Id = E.R_Id
    GROUP BY
        MONTH(E.E_Date),
        L.L_Name,
        R.R_Id,
        R.R_Name
)
-- 筛选符合条件的记录
SELECT * FROM monthly_stats
WHERE 个人消费总金额 >= 地点当月人均支出;

如果你的数据库不支持CTE语法，也可以把子查询放到FROM子句中实现相同逻辑

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