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

Question

我有一个像这样的表：

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

我希望将此限制仅适用于在value列中既具有A行又具有B行的id。在这种情况下，该表格应如下所示：

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

...因为在value列中，既没有ID 2也没有ID 3同时包含A和B。

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

- Andrew LaPrise

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'
    ) 

- Clodoaldo Neto

Answer 2

其中一种方法是计算id具有的a/b的不同数量：

SELECT *
FROM   mytable
WHERE  id IN (SELECT   id
              FROM     mytable
              WHERE    value in ('a', 'b')
              GROUP BY id
              HAVING   COUNT(DISTINCT value) = 2)

- Mureinik