完全匹配两个多对多表的记录集 [英] Fully matching sets of records of two many-to-many tables

问题描述

我有用户、职位和许可证.

关系是:

• 用户可能拥有许多许可证
• 职位可能需要许多许可证

因此我可以轻松获得每个职位的许可要求以及每个用户的有效许可.

So I can easily get license requirements per position(s) as well as effective licenses per user(s).

但我想知道匹配这两个集合的最佳方法是什么?按照逻辑，用户至少需要某个职位所需的那些许可证.可能有更多，但剩余的无关紧要.

But I wonder what would be the best way to match the two sets? As logic goes user needs at least those licenses that are required by a certain position. May have more, but remaining are not relevant.

我想获得用户和符合条件的职位的结果.

PersonID PositionID
1        1          -> user 1 is eligible to work on position 1
1        2          -> user 1 is eligible to work on position 2
2        1          -> user 2 is eligible to work on position 1
3        2          -> user 3 is eligible to work on position 2
4        ...

如您所见，我需要为所有用户提供结果，而不是每次调用一个结果，这会使事情变得容易得多.

As you can see I need a result for all users, not a single one per call, which would make things much much easier.

这里实际上有5张桌子:

There are actually 5 tables here:

create table Person ( PersonID, ...)
create table Position (PositionID, ...)
create table License (LicenseID, ...)

和关系

create table PersonLicense (PersonID, LicenseID, ...)
create table PositionLicense (PositionID, LicenseID, ...)

所以基本上我需要找到某个特定人员获得许可工作的职位.这里当然还有一个更复杂的问题，因为还有其他因素，但主要目标是一样的:

So basically I need to find positions that a particular person is licensed to work on. There's of course a much more complex problem here, because there are other factors, but the main objective is the same:

如何将一个关系表的多个记录与另一个关系表的多个记录匹配.这也可以描述为每组记录的 inner join，而不是每条记录，因为它通常在 TSQL 中完成.

How do I match multiple records of one relational table to multiple records of the other. This could as well be described as an inner join per set of records and not per single record as it's usually done in TSQL.

我正在考虑 TSQL 语言结构:

I'm thinking of TSQL language constructs:

• 行集，但我以前从未使用过它们，也不知道如何使用它们
• intersect 语句可能虽然这些语句可能只适用于整个集合而不是组

• rowsets but I've never used them before and don't know how to use them anyway
• intersect statements maybe although these probably only work over whole sets and not groups

推荐答案

最终解决方案(供以后参考)

与此同时，当你们开发人员回答我的问题时，这是我想出的并使用 CTE 和分区，当然可以在 SQL Server 2008 R2 上使用.我以前从未使用过结果分区，所以我不得不学习一些新东西(这完全是一个优点).代码如下:

Final solution (for future reference)

In the meantime while you fellow developers answered my question, this is something I came up with and uses CTEs and partitioning which can of course be used on SQL Server 2008 R2. I've never used result partitioning before so I had to learn something new (which is a plus altogether). Here's the code:

with CTEPositionLicense as (
    select
        PositionID,
        LicenseID,
        checksum_agg(LicenseID) over (partition by PositionID) as RequiredHash
    from PositionLicense
)
select per.PersonID, pos.PositionID
from CTEPositionLicense pos
    join PersonLicense per
    on (per.LicenseID = pos.LicenseID)
group by pos.PositionID, pos.RequiredHash, per.PersonID
having pos.RequiredHash = checksum_agg(per.LicenseID)
order by per.PersonID, pos.PositionID;

所以我对这三种我命名的技术进行了比较:

So I made a comparison between these three techniques that I named as:

1. 交叉连接(作者 Andriy M)
2. 表变量(佩塔尔·伊万诺夫)
3. 校验和 - 这里是这个(作者 Robert Koritnik，我)

1. Cross join (by Andriy M)
2. Table variable (by Petar Ivanov)
3. Checksum - this one here (by Robert Koritnik, me)

我已经为每个人和每个职位订购了结果，所以我也将相同的结果添加到其他两个以使返回相同的结果.

Mine already orders results per person and position, so I also added the same to the other two to make return identical results.

1. 校验和:7%
2. 表变量:2%(表创建)+ 9%(执行)= 11%
3. 交叉加入:82%

我还将 Table variable 版本更改为 CTE 版本(而不是使用 CTE 的 table 变量)并在最后删除 order by 并比较他们估计的执行计划.仅供参考 CTE 版本为 43%，而原始版本为 53% (10% + 43%).

I also changed Table variable version into a CTE version (instead of table variable a CTE was used) and removed order by at the end and compared their estimated execution plans. Just for reference CTE version 43% while original version had 53% (10% + 43%).

这篇关于完全匹配两个多对多表的记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！