/*1，判断集合A是否属于集合B
* 2，判断集合A是否等于集合B
若集合wa属于集合wb,那么wa的任意一个元素都会在集合wb中存在,并且wb的元素数量要>=wa的元素数量*/

WITH wa AS (
SELECT 'a1' product
UNION ALL
SELECT 'b2'
),
wb AS (
SELECT 'Ta' TYPE,'a1' product
UNION ALL
SELECT 'Ta' ,'b2'
UNION ALL
SELECT 'Tb' ,'a1'
UNION ALL
SELECT 'Tb' ,'b2'
UNION ALL
SELECT 'Tb' ,'c3'
UNION ALL
SELECT 'Tc' ,'a1'
UNION ALL
SELECT 'Td' ,'b2'     
UNION ALL
SELECT 'Td' ,'c3'  
UNION ALL
SELECT 'Te' ,'a1'
UNION ALL
SELECT 'Te' ,'a1'
UNION ALL
SELECT 'Tf' ,'a1'
UNION ALL
SELECT 'Tf' ,'d1'
)

/*若集合wa属于集合wb,那么wa的任意一个元素都会在集合wb中存在,并且wb的元素数量要>=wa的元素数量*/

/* 1,集合属于判断*/
SELECT b.type
FROM wb b(NOLOCK)
JOIN wa a(NOLOCK) ON b.product = a.product  --取出至少包含wa一个元素的所有集合
GROUP BY b.type
HAVING COUNT(DISTINCT b.product) = (SELECT COUNT(1) FROM wa) --保证wa的任意一个元素都会在集合wb中存在                                                         --
ORDER BY b.type

/*2,集合等于判断
SELECT b.type
FROM wb b(NOLOCK)
JOIN wa a(NOLOCK) ON b.product = a.product  --取出至少包含wa一个元素的所有集合
WHERE  ( SELECT COUNT(1) FROM wb WHERE wb.type = b.type  ) = ( SELECT COUNT(1) FROM wa) --集合数量要相等
GROUP BY b.type
HAVING COUNT(DISTINCT b.product) = (SELECT COUNT(1) FROM wa) --保证wa的任意一个元素都会在集合wb中存在                                                               --
ORDER BY b.type


*/