两个多对多表的完全匹配记录集 [英] 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. 交叉连接(安德烈 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%

我还将表变量版本改为CTE版本(而不是使用CTE的表变量)并在最后删除了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屋！