sql - 获取具有列组合的记录

Question

这是我的示例表

CREATE TABLE agentskills 
( 
    id          INT PRIMARY KEY NOT NULL IDENTITY, 
    skilluserid INT, 
    skillid     INT NOT NULL, 
    skilltypeid INT, 
    priority    INT DEFAULT 0 
)

这些是我的示例数据

INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (1,21,1,1)
INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (1,22,1,1)

INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (2,23,1,1)
INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (2,24,1,1)

INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (3,21,1,1)
INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (3,22,1,1)

这里，'skilluserid'是具有技能'skillid'的用户的ID

如何获取具有 Skillid 组合的用户/skilluserid？

例如:获取技能 ID 为 21 和 22 或 22 和 21 或 21,22,23 的用户。具有 21 和 22 的任意组合。

Answer 1

您可以使用聚合和having:

select skilluserid
from agentskills
where skillid in (21, 22)
group by skilluserid
having count(distinct skillid) = 2;  -- "2" = size of list in where clause