获取那些与另一个表中所有记录匹配的记录

在这个问题的范围内，我有3个实体： User Position License 然后我有两个关系（多对多）表： PositionLicense - 这个连接Position和Licenseie。特定职位需要哪些许可证 UserLicense - 这个连接User和Licenseie。授予特定用户的许可。但附加复杂：用户许可证的有效日期范围（ValidFrom和ValidTo）问题这些是输入变量： UserID标识特定的User RangeFrom定义了较低的日期范围限制 RangeTo定义了上限日期范围我需要得到什么？对于特定用户（和日期范围），我需要获取此特定用户可以使用的职位列表。问题是用户必须至少拥有每个匹配位置所需的所有许可证。我在编写SQL查询以获取此列表时遇到了很大的问题。如果可能的话我想使用单个SQL查询（当然可以有其他CTE）。如果你能说服我在几个查询中这样做会更有效率我愿意听。一些可行的数据复制并运行此脚本。 3个用户，3个职位，6个许可证。马克和约翰应该有比赛而不是简。

create table [User] (
    UserID int identity not null
        primary key,
    Name nvarchar(100) not null
)
go

create table Position (
    PositionID int identity not null
        primary key,
    Name nvarchar(100) not null
)
go

create table License (
    LicenseID int identity not null
        primary key,
    Name nvarchar(100) not null
)
go

create table UserLicense (
    UserID int not null
        references [User](UserID),
    LicenseID int not null
        references License(LicenseID),
    ValidFrom date not null,
    ValidTo date not null,
    check (ValidFrom < ValidTo),
    primary key (UserID, LicenseID)
)
go

create table PositionLicense (
    PositionID int not null
        references Position(PositionID),
    LicenseID int not null
        references License(LicenseID),
    primary key (PositionID, LicenseID)
)
go

insert [User] (Name) values ('Mark the mechanic');
insert [User] (Name) values ('John the pilot');
insert [User] (Name) values ('Jane only has arts PhD but not medical.');

insert Position (Name) values ('Mechanic');
insert Position (Name) values ('Pilot');
insert Position (Name) values ('Doctor');

insert License (Name) values ('Mecha');
insert License (Name) values ('Flying');
insert License (Name) values ('Medicine');
insert License (Name) values ('PhD');
insert License (Name) values ('Phycho');
insert License (Name) values ('Arts');

insert PositionLicense (PositionID, LicenseID) values (1, 1);
insert PositionLicense (PositionID, LicenseID) values (2, 2);
insert PositionLicense (PositionID, LicenseID) values (2, 5);
insert PositionLicense (PositionID, LicenseID) values (3, 3);
insert PositionLicense (PositionID, LicenseID) values (3, 4);

insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (1, 1, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (2, 2, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (2, 5, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (3, 4, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (3, 6, '20110101', '20120101');

得到的解决方案我根据已接受的答案设置了我的最终解决方案，为此问题提供了最简化的解决方案。如果你想玩查询只需点击编辑/克隆（无论你是否登录）。什么可以改变：三个变量：两个变量来设置日期范围（@From和@To）用户名（@User）您可以在第一个CTE中切换注释代码，以在完全重叠的用户许可证或部分重叠的用户许可证之间切换代码。

已邀请:

3 个回复

疾桓

这会产生许多假设（忽略日期时间列中存在的时间，假定主键相当明显）并跳过连接以引入用户名，位置详细信息等。（并且你暗示用户必须在指定的整个期间内持有所有许可证，对吗？）

SELECT pl.PositionId
 from PositionLicense pl
  left outer join (--  All licenses user has for the entirety (sp?) of the specified date range
                   select LicenseId
                    from UserLicense
                    where UserId = @UserId
                     and @RangeFrom <= ValidFrom
                     and @RangeTo >= ValidTo) li
   on li.LicenseId = pl.LicenseId
 group by pl.PositionId 
 --  Where all licenses required by position are held by user
 having count(pl.LicenseId) = count(li.LicenseId)

没有数据所以我无法调试或测试它，但是这个或非常接近它的东西应该可以解决问题。

播匣扦阔食

Select ...
From User As U
    Cross Join Position As P
Where Exists    (
                Select 1
                From PositionLicense As PL1
                    Join UserLicense As UL1
                        On UL1.LicenseId = PL1.LicenseId
                            And UL1.ValidFrom <= @RangeTo
                            And UL1.ValidTo >= @RangeFrom
                Where PL1.PositionId = P.Id
                    And UL1.UserId = U.Id
                Except
                Select 1
                From PositionLicense As PL2
                    Left Join UserLicense As UL2
                        On UL2.LicenseId = PL2.LicenseId
                            And UL2.ValidFrom <= @RangeTo
                            And UL2.ValidTo >= @RangeFrom
                            And UL2.UserId = U.Id
                Where PL2.PositionId = P.Id
                    And UL2.UserId Is Null
                )

如果要求您希望用户和位置在整个范围内有效，那就更棘手了：

With Calendar As 
    (
    Select @RangeFrom As [Date]
    Union All
    Select DateAdd(d, 1, [Date])
    From Calendar
    Where [Date] <= @RangeTo
    )
Select ...
From User As U
    Cross Join Position As P
Where Exists    (
                Select 1
                From UserLicense As UL1
                    Join PositionLicense As PL1
                        On PL1.LicenseId = UL1.LicenseId
                Where UL1.UserId = U.Id
                    And PL1.PositionId = P.Id
                    And UL1.ValidFrom <= @RangeTo
                    And UL1.ValidTo >= @RangeFrom
                Except
                Select 1
                From Calendar As C1
                    Cross Join User As U1
                    Cross Join PositionLicense As PL1
                Where U1.Id = U.Id
                    And PL1.PositionId = P.Id
                    And Not Exists  (
                                    Select 1
                                    From UserLicense As UL2
                                    Where UL2.LicenseId = PL1.LicenseId
                                        And UL1.UserId = U1.Id
                                        And C1.Date Between UL2.ValidFrom And UL2.ValidTo
                                    )
                )
Option ( MaxRecursion 0 );

弦砂牧扁

Runnable版本

WITH PositionRequirements AS (
    SELECT p.PositionID, COUNT(*) AS LicenseCt
    FROM #Position AS p
    INNER JOIN #PositionLicense AS posl
        ON posl.PositionID = p.PositionID
    GROUP BY p.PositionID
)
,Satisfied AS (
    SELECT u.UserID, posl.PositionID, COUNT(*) AS LicenseCt
    FROM #User AS u
    INNER JOIN #UserLicense AS perl
        ON perl.UserID = u.UserID
        -- AND @Date BETWEEN perl.ValidFrom AND perl.ValidTo
        AND '20110101' BETWEEN perl.ValidFrom AND perl.ValidTo
    INNER JOIN #PositionLicense AS posl
        ON posl.LicenseID = perl.LicenseID
    -- WHERE u.UserID = @UserID -- Not strictly necessary, we can go over all people
    GROUP BY u.UserID, posl.PositionID
)
SELECT PositionRequirements.PositionID, Satisfied.UserID
FROM PositionRequirements
INNER JOIN Satisfied
    ON Satisfied.PositionID = PositionRequirements.PositionID
    AND PositionRequirements.LicenseCt = Satisfied.LicenseCt

您可以将其转换为在生效日期参数化的内联表值函数。

要回复问题请先登录或注册

获取那些与另一个表中所有记录匹配的记录

3 个回复

发起人

tsql

sql_server_2008_r2

问题状态

获取那些与另一个表中所有记录匹配的记录

与内容相关的链接

3 个回复

发起人

tsql

sql_server_2008_r2

问题状态