第一种: ---测试数据 ;WITH tab1(chef,dish)AS( select 'A','Mint' union all select 'B','Upside' union all select 'B','Creme' union all select 'B','Mint' union all select 'C','Upside' union all select 'C','Creme' union ALL select 'D','Apple' union all select 'D','Upside' union ALL select 'D','Creme' union ALL select 'E','Apple' union all select 'E','Upside' union ALL select 'E','Creme' union ALL select 'E','Bananas' ),tab2(dish)AS( select 'Apple' union all select 'Upside' union ALL select 'Creme' )--测试数据结束 --查询语句 SELECT a.chef FROM tab1 a INNER JOIN tab2 b ON b.dish = a.dish GROUP BY a.chef HAVING COUNT(1) = ( SELECT COUNT(1) FROM tab2 ); 结果:
第二种: ---测试数据 ;WITH tab1(chef,dish)AS( select 'A','Mint' union all select 'B','Upside' union all select 'B','Creme' union all select 'B','Mint' union all select 'C','Upside' union all select 'C','Creme' union ALL select 'D','Apple' union all select 'D','Upside' union ALL select 'D','Creme' union ALL select 'E','Apple' union all select 'E','Upside' union ALL select 'E','Creme' union ALL select 'E','Bananas' ),tab2(dish)AS( select 'Apple' union all select 'Upside' union ALL select 'Creme' )--测试数据结束 --查询语句 SELECT a.chef FROM tab1 a WHERE EXISTS ( SELECT * FROM tab2 b WHERE a.dish = b.dish ) GROUP BY a.chef HAVING COUNT(1) = ( SELECT COUNT(1) FROM tab2 );结果:
第三种: ---测试数据 ;WITH tab1(chef,dish)AS( select 'A','Mint' union all select 'B','Upside' union all select 'B','Creme' union all select 'B','Mint' union all select 'C','Upside' union all select 'C','Creme' union ALL select 'D','Apple' union all select 'D','Upside' union ALL select 'D','Creme' union ALL select 'E','Apple' union all select 'E','Upside' union ALL select 'E','Creme' union ALL select 'E','Bananas' ),tab2(dish)AS( select 'Apple' union all select 'Upside' union ALL select 'Creme' )--测试数据结束 --查询语句 SELECT a.chef FROM tab1 a CROSS APPLY ( SELECT * FROM tab2 b WHERE a.dish = b.dish )t GROUP BY a.chef HAVING COUNT(1) = ( SELECT COUNT(1) FROM tab2 );结果: 不知道是不是楼主说的三种完全不同的方法
---测试数据
;WITH tab1(chef,dish)AS(
select 'A','Mint' union all
select 'B','Upside' union all
select 'B','Creme' union all
select 'B','Mint' union all
select 'C','Upside' union all
select 'C','Creme' union ALL
select 'D','Apple' union all
select 'D','Upside' union ALL
select 'D','Creme' union ALL
select 'E','Apple' union all
select 'E','Upside' union ALL
select 'E','Creme' union ALL
select 'E','Bananas'
),tab2(dish)AS(
select 'Apple' union all
select 'Upside' union ALL
select 'Creme'
)--测试数据结束
--查询语句
SELECT a.chef
FROM tab1 a
INNER JOIN tab2 b ON b.dish = a.dish
GROUP BY a.chef
HAVING COUNT(1) = ( SELECT COUNT(1)
FROM tab2
);
结果:
---测试数据
;WITH tab1(chef,dish)AS(
select 'A','Mint' union all
select 'B','Upside' union all
select 'B','Creme' union all
select 'B','Mint' union all
select 'C','Upside' union all
select 'C','Creme' union ALL
select 'D','Apple' union all
select 'D','Upside' union ALL
select 'D','Creme' union ALL
select 'E','Apple' union all
select 'E','Upside' union ALL
select 'E','Creme' union ALL
select 'E','Bananas'
),tab2(dish)AS(
select 'Apple' union all
select 'Upside' union ALL
select 'Creme'
)--测试数据结束
--查询语句
SELECT a.chef
FROM tab1 a
WHERE EXISTS ( SELECT *
FROM tab2 b
WHERE a.dish = b.dish )
GROUP BY a.chef
HAVING COUNT(1) = ( SELECT COUNT(1)
FROM tab2
);结果:
---测试数据
;WITH tab1(chef,dish)AS(
select 'A','Mint' union all
select 'B','Upside' union all
select 'B','Creme' union all
select 'B','Mint' union all
select 'C','Upside' union all
select 'C','Creme' union ALL
select 'D','Apple' union all
select 'D','Upside' union ALL
select 'D','Creme' union ALL
select 'E','Apple' union all
select 'E','Upside' union ALL
select 'E','Creme' union ALL
select 'E','Bananas'
),tab2(dish)AS(
select 'Apple' union all
select 'Upside' union ALL
select 'Creme'
)--测试数据结束
--查询语句
SELECT a.chef
FROM tab1 a
CROSS APPLY ( SELECT *
FROM tab2 b
WHERE a.dish = b.dish )t
GROUP BY a.chef
HAVING COUNT(1) = ( SELECT COUNT(1)
FROM tab2
);结果:
不知道是不是楼主说的三种完全不同的方法