mysql - 如何从具有唯一 ID 的行中获取 ID？

Question

我有一个这样的表:

id | customer_id | code
-----------------------
1  |  1          | A
2  |  1          | B
3  |  2          | A
4  |  2          | D
5  |  3          | B
6  |  3          | C
6  |  3          | D

我需要一个 SQL 查询来返回代码等于 A 和 B 的所有客户 ID。在上面的数据中，这只会是 customer_id 1。

如果每个代码都有自己的列，这将是一个简单的查询:SELECT DISTINCT customer_id FROM tablename WHERE code = A AND code = B。但是，我似乎无法在多行上制作它。

Answer 1

您可以将 GROUP BY customer_id 与 HAVING 子句一起使用:

select customer_id
from yourtable
where code in ('A', 'B')
group by customer_id
having count(distinct code) = 2

参见 SQL Fiddle with Demo

如果您想从表中返回更多数据，则可以将查询扩展为:

select *
from yourtable t1
where exists (select customer_id
              from yourtable t2
              where code in ('A', 'B')
                and t1.customer_id = t2.customer_id
              group by customer_id
              having count(distinct code) = 2)

参见 SQL Fiddle with Demo