时间戳

首页 > 解决方案 > 如何选择 TableA 中不在 TableB 中但在过滤器组中的所有记录

sql - 如何选择 TableA 中不在 TableB 中但在过滤器组中的所有记录

问题描述

我有 3 个表，Student、Group 和 StudentGroup。
学生可以有超过 1 个组。
样本数据

Student
Id  | Name
01    StudentA     
02    StudentB
03    StudentC

Group
Id  | Name
11    Group1
12    Group2
13    Group3

StudentGroup
Id  | Group_Id   | Group_Name   | Student_id | Student_Name
21         11      Group1             01       StudentA
22         11      Group1             02       StudentB
23         12      Group2             03       StudentC
24         13      Group3             02       StudentB    
25         11      Group1             03       StudentC    
26         13      Group3             01       StudentA

我怎样才能让一些学生与某些选定的所有组没有关系。
==== Sample1 ====
学生 = 02,03
组 = 12,13

我选择 (02,03)Student 和 (12,13)​​Group。
那么我只想要在 StudentGroup 中没有选择组 (12,13)​​ 的学生记录。

StudentGroup
Id  | Group_Id   | Group_Name   | Student_id | Student_Name
23         12      Group2             03       StudentC
24         13      Group3             02       StudentB
26         13      Group3             01       StudentA

预期结果

StudentB
StudentC

选择的组是 12,13
不要得到 StudentA 因为我们选择学生 02,03
得到 StudentB 因为它与 Group3 相关但不与 Group2 相关（选择的组 = 12,13）
得到 StudentC 原因相同 StudentB

==== Sample2 ====
学生 = 01,03
组 = 11,13

StudentGroup
Id  | Group_Id   | Group_Name   | Student_id | Student_Name
21         11      Group1             01       StudentA
22         11      Group1             02       StudentB
24         13      Group3             02       StudentB    
25         11      Group1             03       StudentC    
26         13      Group3             01       StudentA

预期结果

StudentC(03)

我们选择 Student 01 ,03，
但 StudentA(01) 与所有选定的 Group(11,13) 相关，因此不需要 StudentA。
StudentC(03) 与 Group1 相关，但与 Group3 不相关，因此获取 StudentC。

然后我尝试

SELECT * FROM Student
WHERE Id in ('02','03')
AND Id NOT IN (
        SELECT SG.Student_id
        FROM Group G
        LEFT JOIN StudentGroup SG
        ON G.Id = SG.Group_Id
        WHERE G.Id IN ('12','13') 
 )

但是听起来好像不起作用，因为只有学生在学生组中没有数据。
我尝试 在 Student_id = Null （意味着不在 StudentGroup 中）的相同等级中
使用预期获得 Student_id。但是当它为空时，我无法得到它。RANK () OVER ( PARTITION BY Student_id ) 

StudentGroup
Id  | Group_Id   | Group_Name   | Student_id | Student_Name
           11      Group1             03       StudentA
           12      Group2             03       StudentC
           13      Group3             NULL     NULL

那么我该如何实现呢？感谢任何进入主题，阅读和回答的人。

标签： sqlsql-servertsql

解决方案

由于studentgroup表两者都有student_id，所以不需要加入列表group_id。group

通过内部连接Student和StudentGroup带有 where 子句的表格，我们得到了属于任何这些组的学生。但是要删除在两个组中都可用的学生，我们使用了having子句。

（请不要使用保留字作为表或列名。这不是一个好习惯。组是保留字。）

 create table Student(Id  varchar(10), Name varchar(50));
 insert into Student values('01'    ,'StudentA');     
 insert into Student values('02'    ,'StudentB');
 insert into Student values('03'    ,'StudentC');
 
 create table [Group] (Id  int, Name varchar(50));
 insert into [Group] values(11 ,   'Group1');
 insert into [Group] values(12 ,   'Group2');
 insert into [Group] values(13 ,   'Group3');
 
 create table StudentGroup(Id  int, Group_Id   int, Group_Name  varchar(50), Student_id varchar(10), Student_Name varchar(50));
 insert into StudentGroup values(21,         11,      'Group1',             '01'       ,'StudentA');
 insert into StudentGroup values(22,         11,      'Group1',             '02'       ,'StudentB');
 insert into StudentGroup values(23,         12,      'Group2',             '03'       ,'StudentC');
 insert into StudentGroup values(24,         13,      'Group3',             '02'       ,'StudentB'); 
 insert into StudentGroup values(25,         11,      'Group1',             '03'       ,'StudentC');    
 insert into StudentGroup values(26,         13,      'Group3',             '01'       ,'StudentA');

查询 #1

 select s.name from student s inner join studentgroup sg on s.id=sg.student_id
 where s.id in ('02','03') and group_id in (12,13)
 group by s.name
 having count(distinct group_id)<2

输出：

姓名
学生B
学生C

查询 #2

 select s.name from student s inner join studentgroup sg on s.id=sg.student_id
 where s.id in ('01' ,'03') and group_id in (11,13)
 group by s.name
 having count(distinct group_id)<2
 GO

输出：

姓名
学生C

db<小提琴在这里

推荐阅读