表连接问题：查询各剧院消费最高客户信息的SQL实现求助

数据库表结构 #

• Theatre (Theatre#, Name, Address, MainTel)
• Production (P#, Title, ProductionDirector, PlayAuthor)
• Performance (Per#, P#, Theatre#, pDate, pHour, pMinute, Comments)
• Client (Client#, Title, Name, Street, Town, County, telNo, e-mail)
• TicketPurchase (Purchase#, Client#, Per#, PaymentMethod, DeliveryMethod, TotalAmount)

查询需求 #

获取每个剧院的名称，以及在该剧院消费总额最高的客户姓名。

原SQL存在的问题 #

1. 使用过时的逗号表连接语法，可读性差且易出错
2. 子查询错误将单条订单的TotalAmount作为最大值，实际需要统计客户在该剧院的累计消费总额
3. 关联了不需要的Production表，徒增逻辑复杂度
4. 主查询引用了未在FROM子句声明的表P，会直接触发语法错误
5. GROUP BY逻辑错误，TotalAmount不应被纳入分组字段

正确的SQL实现 #

方法1：窗口函数（推荐，简洁高效） #

通过窗口函数为每个剧院的客户消费总额排名，直接筛选出排名第一的记录：

WITH ClientTheatreSpending AS (
    SELECT
        t.Name AS TheatreName,
        c.Name AS ClientName,
        SUM(tp.TotalAmount) AS TotalSpent,
        ROW_NUMBER() OVER (PARTITION BY t.Theatre# ORDER BY SUM(tp.TotalAmount) DESC) AS Rank
    FROM TicketPurchase tp
    JOIN Performance per ON tp.Per# = per.Per#
    JOIN Theatre t ON per.Theatre# = t.Theatre#
    JOIN Client c ON tp.Client# = c.Client#
    GROUP BY t.Theatre#, t.Name, c.Client#, c.Name
)
SELECT TheatreName, ClientName, TotalSpent
FROM ClientTheatreSpending
WHERE Rank = 1;

若存在多个客户在同一剧院消费总额并列最高，可将ROW_NUMBER()替换为RANK()，返回所有符合条件的客户

方法2：子查询分组筛选 #

分步计算客户累计消费、剧院最高消费额，再关联得到对应客户：

-- 第一步：统计每个客户在各剧院的总消费
WITH ClientTheatreTotal AS (
    SELECT
        t.Theatre#,
        t.Name AS TheatreName,
        c.Client#,
        c.Name AS ClientName,
        SUM(tp.TotalAmount) AS TotalSpent
    FROM TicketPurchase tp
    JOIN Performance per ON tp.Per# = per.Per#
    JOIN Theatre t ON per.Theatre# = t.Theatre#
    JOIN Client c ON tp.Client# = c.Client#
    GROUP BY t.Theatre#, t.Name, c.Client#, c.Name
),
-- 第二步：找出每个剧院的最高消费额
TheatreMaxSpending AS (
    SELECT Theatre#, MAX(TotalSpent) AS MaxSpent
    FROM ClientTheatreTotal
    GROUP BY Theatre#
)
-- 第三步：关联匹配对应客户
SELECT ctt.TheatreName, ctt.ClientName, ctt.TotalSpent
FROM ClientTheatreTotal ctt
JOIN TheatreMaxSpending tms ON ctt.Theatre# = tms.Theatre# AND ctt.TotalSpent = tms.MaxSpent;

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