MYSQL检查一组中的所有值是否与另一组匹配 [英] MYSQL checking all values in a set match another set

问题描述

我有两个表A和B，其值都低于

I have two tables A and B with values below

表A

   X
------
1
2
3

表B

X       Y
------  ------
1       A
2       A
3       A
1       B
2       B
1       C
3       D

我只需要从表B中找到与表A中所有值匹配的Y值. 因此，对于上面的示例，唯一匹配的Y值是A(A的X值分别为1,2和3)

I need to find only the Y values from Table B which match All of the values in Table A. So for the above example the only Y value that matches is A (A has an X value of 1,2,and 3)

推荐答案

这是"set-within-sets"子查询的示例.我喜欢通过聚合和having子句来解决这个问题.

This is an example of a "set-within-sets" subquery. I like to approach this with aggregation and a having clause.

select b.y
from tableB b join
     tableA a
     on b.X = a.X
group by b.y
having count(distinct b.x) = (select count(*) from tableA);

这篇关于MYSQL检查一组中的所有值是否与另一组匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！