代码之家 › 专栏 › 技术社区 › gh9

通过多个查找表子句查找数据

sql-server sql

gh9 · 技术社区 · 3 年前

declare @Character table (id int, [name] varchar(12));

insert into @Character (id, [name])
values
(1, 'tom'),
(2, 'jerry'),
(3, 'dog');

declare @NameToCharacter table (id int, nameId int, characterId int);

insert into @NameToCharacter (id, nameId, characterId)
values
(1, 1, 1),
(2, 1, 3),
(3, 1, 2),
(4, 2, 1);

Name Table不仅仅有1,2,3,而且要解析的列表是动态的

名称表

id  | name
----------
1      foo
2      bar
3      steak

CharacterTable

id | name
---------
1     tom
2     jerry
3     dog

名称到字符表

id | nameId | characterId
1     1           1
2     1           3
3     1           2
4     2           1

我正在寻找一个查询,它将返回一个有两个名称的字符。例如对于以上数据,只会返回“tom”。

SELECT * 
FROM nameToCharacterTable
WHERE nameId in (1,2)

in子句将返回具有1或3的每一行。我只想返回那些 同时具有1和3。

我被难住了,我已经尝试了我所知道的一切,不想求助于动态SQL。任何帮助都会很好

本例中的1,3将是一个动态整数列表。例如,它可以是1,3,4,5,。。。。。

1 回复 | 直到 3 年前

Dale K 3 年前

过滤出CharacterToName表中与您提供的列表匹配的Character出现次数(我假设您可以将其转换为表变量或临时表),例如。

declare @Character table (id int, [name] varchar(12));

insert into @Character (id, [name])
values
(1, 'tom'),
(2, 'jerry'),
(3, 'dog');

declare @NameToCharacter table (id int, nameId int, characterId int);

insert into @NameToCharacter (id, nameId, characterId)
values
(1, 1, 1),
(2, 1, 3),
(3, 1, 2),
(4, 2, 1);

declare @RequiredNames table (nameId int);

insert into @RequiredNames (nameId)
values
(1),
(2);

select *
from @Character C
where (
    select count(*)
    from @NameToCharacter NC
    where NC.characterId = c.id
    and NC.nameId in (select nameId from @RequiredNames)
) = 2;

退货:

身份证件	名称
1.	汤姆

注意:如图所示,提供DDL+DML使人们更容易为您提供帮助。

Charlieface 3 年前

这是经典 Relational Division 带剩余部分 .

有许多不同的解决方案。 @DaleK has given you 一个很好的方法是:内部连接所有内容,然后检查每组是否有正确的数量。这通常是最快的解决方案。

如果您想确保它与动态数量的行一起工作,只需将最后一行更改为

) = (SELECT COUNT(*) FROM @RequiredNames);

存在另外两种常见的解决方案。

左联接并检查所有行是否已联接

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM @RequiredNames rn
    LEFT JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = COUNT(nc.nameId)  -- all rows are joined
);

双重反联接,换句话说:不存在“不在集合中”的“必需”

SELECT *
FROM @Character c
WHERE NOT EXISTS (SELECT 1
    FROM @RequiredNames rn
    WHERE NOT EXISTS (SELECT 1
        FROM @NameToCharacter nc
        WHERE nc.nameId = rn.nameId AND nc.characterId = c.id
    )
);

一个与另一个答案的变体使用窗口聚合而不是子查询。我不认为这是表演性的,但它可能在某些情况下有用途。

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM (
      SELECT *, COUNT(*) OVER () AS cnt
      FROM @RequiredNames
    ) rn
    JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = MIN(rn.cnt)
);

db<>fiddle