sql - 查找具有值 A 的行和值 B 的行的所有 ID

Question

我有一个这样的表:

+-----+-------+-----+
| id  | value | ... |
+-----+-------+-----+
| 1   | A     | ... |
| 1   | B     | ... |
| 1   | C     | ... |
| 2   | B     | ... |
| 2   | C     | ... |
| 3   | A     | ... |
| 3   | C     | ... |
| 4   | B     | ... |
| 4   | A     | ... |
| ... | ...   | ... |
+-----+-------+-----+

我想将其限制为仅 id 在 value 列中具有 A 行和 B 行的 id。在这种情况下，表格将如下所示:

+-----+-------+-----+
| id  | value | ... |
+-----+-------+-----+
| 1   | A     | ... |
| 1   | B     | ... |
| 1   | C     | ... |
| 4   | B     | ... |
| 4   | A     | ... |
| ... | ...   | ... |
+-----+-------+-----+

... 因为 id 2 和 3 在 value 列中都没有 A 和 B。

是否有一种简洁的方法来定位这些 ID？

Answer 1

select id, value
from t
where id in (
    select id
    from t
    group by id
    having bool_or(value = 'A') and bool_or(value = 'B')
)

或

select id, value
from t t0
where 
    exists (
        select 1
        from t
        where id = t0.id and value = 'A'
    ) and
    exists (
        select 1
        from t
        where id = t0.id and value = 'B'
    )