如何在SQLite中对计算布尔表达式进行GROUP BY

Question

我有一个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，那将对超出我需要的内容进行分区，并在每场演出中返回多达8个组。我只想用布尔值"进行分组；soldout或NOT soldout"；。

如何在SQLite中做到这一点？

Answer 1

删除WHERE子句，它不仅过滤掉'soldout'以外的任何状态，而且还将LEFT联接更改为INNER联接，因为它还过滤掉任何不匹配的行

您应该使用带有聚合函数SUM()的条件聚合(如果使用不带HAVING子句的查询，则使用TOTAL()(来获取列sellout_count和COUNT()，以获取性能天数：

SELECT s.id, 
s.name, 
SUM(ps.name = 'soldout') AS sellout_count,
COUNT(p.id) performance_days -- or COUNT(DISTINCT p.id)
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

Answer 2

您可以使用以下子查询：

SELECT s.id,
s.name,
COUNT(p.id) AS sellout_count,
(SELECT COUNT(p1.id)
FROM performances AS p1
WHERE p.show_id = p1.show_id
) AS performance_days
...