已分组场景下的SQL二次分组：按售票量统计节日规模数量

原SQL存在的问题 #

• CASE语法错误：多个独立CASE语句不符合SQL规范，需合并为单个CASE结构，每个条件用WHEN...THEN定义返回值，最后用END收尾。
• 缺少节日分组：未先按单个节日聚合售票量，直接全局统计无法得到每个节日的售票数，无法进行规模分类。
• 数据类型错误：数字比较使用了字符串引号（'0'、'250'），会导致字符串逻辑比较，而非数值比较。
• 关联逻辑模糊：原查询中ticket与purchase的关联条件ticket.type = purchase.type可能无法准确关联售票记录，需根据实际表结构调整。

正确SQL示例 #

假设存在festival（节日表）、ticket（票务表）、purchase（购票记录表），表间关联为festival.id = ticket.festival_id、ticket.id = purchase.ticket_id，以下是实现逻辑：

SELECT 
    COUNT(*) AS Total_Festivals,
    CASE
        WHEN total_tickets BETWEEN 0 AND 250 THEN 'Small Festival'
        WHEN total_tickets BETWEEN 251 AND 500 THEN 'Medium Festival'
        WHEN total_tickets BETWEEN 501 AND 750 THEN 'Large Festival'
        WHEN total_tickets > 750 THEN 'XL Festival'
    END AS Festival_Size
FROM (
    -- 子查询：计算每个节日的总售票数
    SELECT 
        festival.id,
        COUNT(purchase.id) AS total_tickets
    FROM festival
    JOIN ticket ON festival.id = ticket.festival_id
    JOIN purchase ON ticket.id = purchase.ticket_id
    GROUP BY festival.id
) AS festival_ticket_counts
GROUP BY Festival_Size
-- 按规模顺序排序，保证结果顺序符合预期
ORDER BY 
    CASE Festival_Size
        WHEN 'Small Festival' THEN 1
        WHEN 'Medium Festival' THEN 2
        WHEN 'Large Festival' THEN 3
        WHEN 'XL Festival' THEN 4
    END;

补充：统计未售票的节日 #

如果需要包含售票量为0的节日（无任何购票记录的节日），将JOIN替换为LEFT JOIN即可：

SELECT 
    COUNT(*) AS Total_Festivals,
    CASE
        WHEN total_tickets BETWEEN 0 AND 250 THEN 'Small Festival'
        WHEN total_tickets BETWEEN 251 AND 500 THEN 'Medium Festival'
        WHEN total_tickets BETWEEN 501 AND 750 THEN 'Large Festival'
        WHEN total_tickets > 750 THEN 'XL Festival'
    END AS Festival_Size
FROM (
    SELECT 
        festival.id,
        COUNT(purchase.id) AS total_tickets
    FROM festival
    LEFT JOIN ticket ON festival.id = ticket.festival_id
    LEFT JOIN purchase ON ticket.id = purchase.ticket_id
    GROUP BY festival.id
) AS festival_ticket_counts
GROUP BY Festival_Size
ORDER BY 
    CASE Festival_Size
        WHEN 'Small Festival' THEN 1
        WHEN 'Medium Festival' THEN 2
        WHEN 'Large Festival' THEN 3
        WHEN 'XL Festival' THEN 4
    END;

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