如何在SQLite中按计算所得布尔表达式执行GROUP BY？

我有一个存储为期一个月节日数据的SQLite数据库，部分表结构如下：

CREATE TABLE IF NOT EXISTS performance_status (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE       -- available, soldout, past, etc
)
CREATE TABLE IF NOT EXISTS performances (
    id INTEGER PRIMARY KEY,
    date INTEGER,           -- The date of the show
    sold_out_date INTEGER,  -- The date on which the showing sold out

    show_id INTEGER,
    status_id INTEGER,
    FOREIGN KEY(show_id) REFERENCES shows(id),
    FOREIGN KEY(status_id) REFERENCES performance_status(id),
    UNIQUE(date, show_id)
)
CREATE TABLE IF NOT EXISTS shows (
    id INTEGER PRIMARY KEY,
    name TEXT
)

我已有一个查询，可获取存在售罄场次的演出列表，并按售罄场次数量排序：

SELECT s.id, s.name, count(p.id) AS sellout_count
FROM shows AS s
    LEFT JOIN performances AS p ON s.id = p.show_id
    LEFT JOIN performance_status AS ps ON p.status_id = ps.id
WHERE ps.name = "soldout"
GROUP BY s.id
HAVING sellout_count > 0
ORDER BY sellout_count DESC
LIMIT 3

该查询返回结果如下：

[(2100, 'Show 1', 25), (1286, 'Show 2', 25), (2936, 'Show 3', 24)]

现在需要统计每个演出的总场次（无论是否售罄），但当前WHERE子句仅筛选了售罄记录。如果用GROUP BY s.id, ps.name会生成过多分组，我只想按「售罄/未售罄」的布尔逻辑统计，请问在SQLite中该如何实现？

可以通过条件聚合函数实现，无需额外细分分组，直接在SELECT语句中结合COUNT()和CASE语句分别统计总场次与售罄场次，同时保留原有的筛选和排序逻辑：

SELECT 
    s.id, 
    s.name, 
    COUNT(p.id) AS total_performances,
    SUM(CASE WHEN ps.name = 'soldout' THEN 1 ELSE 0 END) AS sellout_count
FROM shows AS s
LEFT JOIN performances AS p ON s.id = p.show_id
LEFT JOIN performance_status AS ps ON p.status_id = ps.id
GROUP BY s.id
HAVING sellout_count > 0
ORDER BY sellout_count DESC
LIMIT 3

逻辑说明： #

• COUNT(p.id)：统计每个演出的总场次，LEFT JOIN会关联所有演出场次，NULL记录会被COUNT()自动忽略；HAVING条件会过滤掉没有售罄场次的演出。
• SUM(CASE WHEN ps.name = 'soldout' THEN 1 ELSE 0 END)：通过条件判断，仅对状态为soldout的场次计数，精准得到售罄场次数量。
• 移除了原查询中WHERE ps.name = 'soldout'的过滤，确保能保留所有场次数据用于总场次统计，再通过HAVING sellout_count > 0锁定存在售罄场次的演出。

如果追求更高性能，可先用EXISTS子查询提前筛选出有在售罄场次的演出，再进行统计，避免聚合后过滤的开销：

SELECT 
    s.id, 
    s.name, 
    COUNT(p.id) AS total_performances,
    SUM(CASE WHEN ps.name = 'soldout' THEN 1 ELSE 0 END) AS sellout_count
FROM shows AS s
LEFT JOIN performances AS p ON s.id = p.show_id
LEFT JOIN performance_status AS ps ON p.status_id = ps.id
WHERE EXISTS (
    SELECT 1 FROM performances p2
    JOIN performance_status ps2 ON p2.status_id = ps2.id
    WHERE p2.show_id = s.id AND ps2.name = 'soldout'
)
GROUP BY s.id
ORDER BY sellout_count DESC
LIMIT 3

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