EXCEPT子查询对未出现在输出中的列名的内部联接

Question

我有一张学生和班级的表格。我想知道从一个学期到另一个学期减少了哪些课程(并添加了类似的课程查询(。

Student    Class       Semester
==============================
Alice      English     11
Alice      Geometry    11
Alice      English     12
Bob        Spanish     11
Bob        Spanish     12

我的方法是使用except(与minus相同(:

select distinct Class
from table
where table.Student = 'Alice'
and table.Semester = 11
except
select distinct Class
from table
where table.Student = 'Alice'
and table.Semester = 12

此操作正常，返回Geometry。然而，我需要将其用作子查询，如下所示：

select Student, string_agg(X.Class, ', ') as 'Deleted_Classes', 
count(X) as 'Num_deleted',
SemesterTable.Semester as semester, 
lag(Semester, 1) 
over (partition by StudentTable.Student 
order by SemesterTable.Semester) as Prev_Semester,
from 
StudentTable
SemesterTable
inner join (
<<<Same query from above>>>
) X on _______
where X.Num_deleted > 0

我的问题是____部分——内部联接只能在输出中出现的列上联接。但我的except查询不会返回上学期和当前学期的值(如果没有删除任何类，它甚至可能根本不返回任何值(。那么，如何将子查询联接到主表中呢？我想要的输出是：

Student     Semester     Prev Semester   Deleted_Classes
========================================================
Alice        12          11              Geometry

Alice出现是因为她的日程安排发生了变化，但Bob被省略是因为他的日程安排没有变化。

Answer 1

我会通过Left Join来完成这项工作，并通过where中的exist来检查特定学生下学期的可用性。

Select T.Student, T.Semester+1 As Semester, T.Semester As [Prev Semester], 
string_agg(T.Class, ',') As Deleted_Classes
From Tbl As T Left Join Tbl As T1 On (T.Student=T1.Student 
And T.Semester+1=T1.Semester
And T.Class=T1.Class)
Where Exists (Select * From Tbl 
Where Student=T.Student 
And Semester=T.Semester+1) And
T1.Semester Is Null
Group by T.Student, T.Semester+1, T.Semester

如果你的学期ID没有严格增加，你可以通过cte和dense_rank使用相同的逻辑，根据你对每个学生的标准订购学期，如下所示：

With CTE As (
Select Student, Semester, Class, 
Dense_Rank() Over (Partition by Student Order by Semester) As N
From Tbl
)
Select T.Student, Max(T2.Semester) As Semester, Max(T.Semester) As [Prev Semester], 
string_agg(T.Class, ',') As Deleted_Classes
From CTE As T Left Join CTE As T1 On (T.Student=T1.Student 
And T.N+1=T1.N
And T.Class=T1.Class)
Cross Apply (Select Distinct Semester 
From CTE 
Where Student=T.Student          
And N=T.N+1) As T2
Where T1.N Is Null
Group by T.Student, T.N+1, T.N

结果：

学生
Alice

with data as (
select *,
min(Semester) over (partition by Student, Class) as minSemester,
max(Semester) over (partition by Student, Class) as maxSemester,
count(*) over (partition by Student, Class) as cntSemester
from T
where Semester in (11, 12)
)
select Student, Class,
case when minSemester = 12 then 'Added'   else '' end as Added,
case when maxSemester = 11 then 'Dropped' else '' end as Dropped
from data
where maxSemester = 11;

你可以从这些值中得到各种各样的信息。例如，知道最近一个学期不是12点就意味着下课了。您可以对添加执行类似操作。

Answer 2

使用NOT EXISTS似乎适用于此。

create table StudentSemesters (
Student varchar(30),
Class varchar(30),
Semester int
);
insert into StudentSemesters
(Student, Class, Semester) values
('Alice',     'English',     11) 
, ('Alice',     'Geometry',    11)
, ('Alice',     'English',     12)
, ('Bob',       'Spanish',     11) 
, ('Bob',       'Spanish',     12) 
;

select Student
, Semester+1 as [Semester] 
, Semester as [Prev_Semester]
, STRING_AGG(Class, ', ') as [Deleted_Classes] 
from StudentSemesters t
where not exists (
select 1
from StudentSemesters t2
where t2.Student = t.Student
and t2.Class = t.Class
and t2.Semester = t.Semester+1
)
and exists (
select 1
from StudentSemesters t2
where t2.Student = t.Student
and t2.Semester = t.Semester+1
)
group by Student, Semester;

期/table>

学生
Alice	12

根据Student对按Student和Semester排序的行进行分组，在每组中，按Semester对行进行分组并执行行间计算。春季学期和秋季学期的课程集之间的差异是新增的课程，秋季学期和春季学期的差异包含取消的课程。用SQL编写代码很麻烦，因为您需要窗口函数CROSSAPPLY和OUTERAPPLY。SQL语句将很长，很难理解。另一种选择是将数据移出数据库，并在Python或SPL中进行处理。SPL是一个开源Java包，很容易集成到Java程序中并生成简单的代码。它只需要两行代码就可以完成这项工作

1	=MSSQL.query@x("从类别顺序中按1,3选择*"(
2	=A1.group@o(#1(.coj(~.group@o(#1，#3；~.(#2((.new(学生，#2[+1]：学期，#2：上一学期，(#3#3[+1](.concat@c((：Deleted_Classes，(#3[+1]#3(.concat@c((：Added_Classes(.m(：-2((