查找另一个集合中的所有集合/实体 [英] Find all sets/entities that are in another set

问题描述

答案在摘要中找到

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:

1. 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.

2. 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:

这篇关于查找另一个集合中的所有集合/实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！