查找多个列重复在SQL

Question

我想找到谁是有subjectID（0,1,2）的batchID只，请帮助我，在此先感谢

我需要回答BatchID = 12，BatchID = 51，我能在SQL

MyTable的

uid BatchID SubjectID 
6 2 0 
7 2 1 
8 2 2 
9 2 4 
10 3 0 
11 3 1 
12 4 5 
13 4 0 
14 5 5 
15 6 5 
17 7 0 
18 7 1 
19 7 2 
26 12 0 
27 12 1 
28 12 2 
29 1 0 
30 1 1 
31 1 4 
62 45 5 
63 46 0 
64 46 1 
65 46 4 
107 49 6 
108 49 2 
109 49 4 
110 50 1 
111 50 3 
116 0 1 
117 0 4 
118 51 0 
119 51 1 
120 51 2 

Answer 1

做可以使用条件汇聚了这一点：

select batchId 
from your_table 
group by batchId 
having count(distinct case when subjectID in (0,1,2) then subjectID end) = 3 
and count(case when subjectID not in (0,1,2) then 1 end) = 0 

说明：

• Group by batchId - 上batchId
• count(distinct case when subjectID in (0,1,2) then subjectID end)总结 - 产生3只有他们三个人都存在这个batchId
• count(case when subjectID not in (0,1,2) then 1 end) - 产生0，如果没有其他subjectID除了0,1， 2假设在subjectId列中不允许有空值。

Answer 2

您可以使用ROW_NUMBER（）

;with cte as (
    select *, RowN = row_number() over (partition by batchid order by uid) from #yourbatch where subjectid in (0,1,2) 
) select distinct BatchId from cte where RowN > 1