时间戳

首页 > 解决方案 > 获取与传入的员工属于完全相同位置列表的所有员工

sql - 获取与传入的员工属于完全相同位置列表的所有员工

问题描述

我有一张桌子叫EmployeeLocationAssn：

CREATE TABLE EmployeeLocationAssn (
[EmployeeLocationAssnId] [int]  IDENTITY(1,1) NOT NULL, 
[EmployeeId] [int] NOT NULL,
[LocationId] [int] NOT NULL
)

此表包含员工及其关联位置的数据。

INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (1, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (1, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (2, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (2, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (3, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (3, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 4)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (5, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (5, 4)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 4)

我想获取与传入的员工 ID 具有完全相同位置列表的所有员工。

示例：如果用户通过EmployeeId = 1，则查询应返回具有相同位置的所有员工。

Output: 
@EmployeeId = 1
1
2
3

员工 4 和 6 具有位置 1、2、3 和 4。它与员工 1 拥有的位置 1 和 2 不完全匹配，而员工 5 具有完全不同的位置列表 (3、4)。

@EmployeeId = 4
4
6

员工 1、2 和 3 具有位置 1 和 2。它与员工 4 具有的位置 1、2、3 和 4 不完全匹配，并且员工 5 具有部分位置列表 (3、4)。只有员工 4 和 6 具有相同的位置列表（1、2、3、4）。

@EmployeeId = 5
5

员工 1、2 和 3 的位置 1 和 2。它与员工 5 的位置 3 和 4 不完全匹配，员工 4 和 6 的位置列表更大（1、2、3、4）。

我开始写一个查询，但很困惑，这就是我所拥有的，这当然是不正确的。

DECLARE @EmployeeId int = 1

Select ELA.EmployeeId, ELA.LocationId from EmployeeLocationAssn ELA
Where not exists 
(Select ELA.LocationId from EmployeeLocationAssn ELA2 where ELA2.EmployeeId = @EmployeeId
EXCEPT
Select ELA.LocationId from EmployeeLocationAssn ELA3 where ELA3.EmployeeId = ELA.EmployeeId)
and ELA.EmployeeId <> @EmployeeId;

标签： sqlsql-serverdatabasetsql

解决方案

您可以使用string_agg（如果使用 SQL Server 2017+）来比较员工：

declare @EmployeeId int = 1;

with cte as (
    select E.EmployeeId
        , string_agg(E.LocationId,',') within group (order by LocationId asc) LocationGroup
    from EmployeeLocationAssn E
    group by E.EmployeeId
)
select EmployeeId
from cte
where LocationGroup = (select LocationGroup from cte where EmployeeId = @EmployeeId);

或者for xml path如果使用较低版本：

declare @EmployeeId int = 1;

with cte as (
  select E.EmployeeId, 
    substring(
      (
        select ',' + convert(varchar(12),E1.LocationId) as [text()]
        from #EmployeeLocationAssn E1
        where E1.EmployeeId = E.EmployeeId
        order by E1.LocationId
        for xml path ('')
      ), 2, 1000) LocationGroup
  from #EmployeeLocationAssn E
  group by E.EmployeeId
)
select EmployeeId
from cte
where LocationGroup = (select LocationGroup from cte where EmployeeId = @EmployeeId);

小提琴手

推荐阅读