查找另一个集合中的所有集合/实体

本文介绍了查找另一个集合中的所有集合/实体的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

答案在摘要中找到

The answer is found in the abstract here but I'm looking for the concrete SQL solution.

Given the following tables:

------------ ----------- | F_Roles | | T_Roles | ------+----- -----+----- | FId | RId| |TId | RId| ------+------ -----+----- | f1 | 2 | | t1 | 1 | | f1 | 3 | | t1 | 2 | | f2 | 2 | | t1 | 3 | | f2 | 4 | | t1 | 4 | | f2 | 9 | | t1 | 5 | | f3 | 6 | | t1 | 6 | | f3 | 7 | | t1 | 7 | ------------ ----------

(F_Roles) is a join table between F (not shown) and Roles (also not shown) (T_Roles) is a join table between T (not shown) and Roles (not shown)

I need to return:

all (DISTINCT) FId's where the set of RId's for a given FId is a subset of (or 'IN') Roles. (I know I'm mixing Set Theory with database terms but only in the interest of better conveying the idea, I hope). So, f1 and f3 should be returned in this case, because the set of RId's for f1, {2,3}, and for f3, {6,7}, are subsets of T_Roles.

the list of RId's in T_Roles NOT found in any of the functions returned above. (T_Roles - (f1 Union f3)), or {1,4,5} in this example.

解决方案

Let's define the following sample data:

DECLARE @F_Roles TABLE ( [FID] CHAR(2) ,[RID] TINYINT ); DECLARE @Roles TABLE ( [RID] TINYINT ); INSERT INTO @F_Roles ([FID], [RID]) VALUES ('f1', 2) ,('f1', 3) ,('f2', 2) ,('f2', 4) ,('f2', 9) ,('f3', 6) ,('f3', 7); INSERT INTO @Roles ([RID]) VALUES (1), (2), (3), (4), (5), (6), (7);

No, the first query can be solved using the T-SQL statement below:

SELECT F.[FID] FROM @F_Roles F LEFT JOIN @Roles R ON F.[RID] = R.[RID] GROUP BY F.[FID] HAVING SUM(CASE WHEN R.[RID] IS NULL THEN 0 ELSE 1 END) = COUNT(F.[RID]);

The idea is pretty simple. We are using LEFT join in order to check which RID from the @F_Roles table has corresponding RID in the @Rolestable. If it has not, the value returned by the query for the corresponding row is NULL. So, we just need to count the RIDs for each FID and to check if this count is equal to the count of values returned by the second table (NULL values are ignored).

The latter query is simple, too. Having the FID from the first, we just can use EXCEPT in order to found RIDs which are not matched:

SELECT [RID] FROM @Roles EXCEPT SELECT [RID] FROM @F_Roles WHERE [FID] IN ( SELECT F.[FID] FROM @F_Roles F LEFT JOIN @Roles R ON F.[RID] = R.[RID] GROUP BY F.[FID] HAVING SUM(CASE WHEN R.[RID] IS NULL THEN 0 ELSE 1 END) = COUNT(F.[RID]) );

Here is the result of the execution of the queries: