MSSQL环境下关联表结合SUM、MAX函数查询最高消费客户的SQL编写问题

符合要求的查询语句 #

SELECT 
    s.CusID,
    s.CusName AS Name,
    s.Sum_OrdTotalPrice AS OrderTotalPrice
FROM (
    SELECT 
        CO.CusID,
        C.CusName,
        SUM(CO.OrderTotalPrice) AS Sum_OrdTotalPrice 
    FROM CustomerOrder CO
    INNER JOIN Customers C ON CO.CusID = C.CusID
    GROUP BY CO.CusID, C.CusName
) s
WHERE s.Sum_OrdTotalPrice = (
    SELECT MAX(Sum_OrdTotalPrice) 
    FROM (
        SELECT SUM(OrderTotalPrice) AS Sum_OrdTotalPrice
        FROM CustomerOrder
        GROUP BY CusID
    ) t
)

逻辑说明 #

• 内层关联子查询先完成两张表的关联，通过SUM函数按客户维度聚合计算每个客户的总消费金额
• WHERE条件中的子查询先对所有客户的消费总金额使用MAX函数得到最高消费值，再用该值筛选客户列表，即可得到总消费最高的客户完整信息
• 本方案未使用TOP函数，完全符合要求，同时支持多个客户总消费并列最高的场景，会返回所有符合最高消费条件的客户记录

可选简洁写法（CTE实现，逻辑完全一致） #

WITH CustomerTotal AS (
    SELECT 
        CO.CusID,
        C.CusName,
        SUM(CO.OrderTotalPrice) AS Sum_OrdTotalPrice 
    FROM CustomerOrder CO
    INNER JOIN Customers C ON CO.CusID = C.CusID
    GROUP BY CO.CusID, C.CusName
)
SELECT 
    CusID,
    CusName AS Name,
    Sum_OrdTotalPrice AS OrderTotalPrice
FROM CustomerTotal
WHERE Sum_OrdTotalPrice = (SELECT MAX(Sum_OrdTotalPrice) FROM CustomerTotal)

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