如何查询sqlite3中包含一个必需元素和至少一个可选元素的所有组

Question

问题&预期结果

假设我有下表t:

id          f_id        col1
----------  ----------  ----------
1           1           B
2           1           C
3           2           A
4           2           C
5           2           D
6           2           E
7           3           A
8           3           D
9           3           E
10          4           A
11          4           B
12          5           C
13          5           D

我想选择所有不同的f_id，使col1包含以下值组合之一：

• A和C
• A和D
• A、C和D

因此，预期结果为：

f_id
----------
2
3

自己的尝试

基于之前的问题，我尝试了以下查询

SELECT f_id
FROM t
WHERE (col1 IN ('A', 'C')) or (col1 in ('A', 'D'))
GROUP BY f_id
HAVING COUNT(distinct col1) >= 2;

然而，这个查询也与包含C和D的组匹配，但不与A匹配。我不希望这样，因为A很重要。上述查询结果如下：

f_id
----------
2
3
5

如何获得所需的结果？

原始脚本

为了方便起见，以下是生成原始表的代码：

drop table if exists t;
CREATE TABLE t (id INTEGER, f_id INTEGER, col1 VARCHAR(1));
INSERT INTO t (id, f_id, col1) VALUES
(1, 1, 'B'),
(2, 1, 'C'),
(3, 2, 'A'),
(4, 2, 'C'),
(5, 2, 'D'),
(6, 2, 'E'),
(7, 3, 'A'),
(8, 3, 'D'),
(9, 3, 'E'),
(10, 4, 'A'),
(11, 4, 'B'),
(12, 5, 'C'),
(13, 5, 'D')
;

Answer 1

首先过滤表中的行，以便只返回col1中包含'A'或'C'或'D'的行和group by f_id
最后在HAVING子句中设置条件，这样您只会得到至少包含1个'A'和任何其他2:的f_id

SELECT f_id
FROM t
WHERE col1 IN ('A', 'C', 'D')
GROUP BY f_id
HAVING SUM(col1 = 'A') > 0
AND COUNT(DISTINCT col1) > 1

如果每个f_id的col1中没有重复项，则可以将COUNT(DISTINCT col1) > 1更改为COUNT(*) > 1

或者，使用EXISTS:

SELECT t1.f_id
FROM t t1
WHERE t1.col1 = 'A'
AND EXISTS (
SELECT 1
FROM t t2
WHERE t2.f_id = t1.f_id AND t2.col1 IN ('C', 'D')
)

请参阅演示。

Answer 2

假设组合的顺序并不重要：

select f_id, group_concat(col1, '') agg
from t
GROUP BY f_id
HAVING (agg LIKE '%A%' AND agg LIKE '%C%')
OR (agg LIKE '%A%' AND agg LIKE '%D%');

可能有更好的比较方法(例如使用regex(。