sql - 如何找到相等的子集？

Question

我有一个带有子集的表。如何找到与给定ID具有相同子集的读者ID？例如:

输入阅读器 = 4
预期输出:阅读器 1 和 5。

子集大小并不总是 = 3，因为在示例中它可以是动态的。什么是正确的 SQL 查询？

declare @t table(
   reader int not null,
   book int,
   pages int
)
insert into @t (reader, book, pages)
select 1, 1, 100 union
select 1, 2, 201 union
select 1, 3, 301 union
select 2, 1, 100 union
select 2, 3, 101 union
select 2, 3, 301 union
select 3, 1, 100 union
select 3, 2, 101 union
select 3, 3, 301 union
select 4, 1, 100 union
select 4, 2, 201 union
select 4, 3, 301 union
select 5, 1, 100 union
select 5, 2, 201 union
select 5, 3, 301

select * from @t

Answer 1

这有点麻烦，但您可以使用自联接:

with t as (
      select t.*, count(*) over (partition by reader) as cnt
      from @t t
     )
select t.reader
from t left join
     t t2
     on t2.book = t.book and
        t2.pages = t.pages and
        t2.cnt = t.cnt and
        t2.reader = 4
group by t.reader, t.cnt
having count(*) = t.cnt and
       count(*) = count(t2.reader);

left join需要避免子集关系。也就是说，拥有“4”的所有书籍以及其他书籍。