未出现在输出中的列名上的EXCEPT子查询的内部联接-编程知识-白鹭情

我有一张学生和班级的表格。我想找出从一个学期到另一个学期取消了哪些课程（并添加了类似的课程查询）。

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被省略，因为他的日程没有变化。

uj5u.com热心网友回复：

我会Left Join通过existin检查特定学生下学期的可用性来做到这一点where。

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 没有严格增加，您可以使用相同的逻辑ctewithdense_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

结果：

学生	学期	上学期	Deleted_Classes
爱丽丝	12	11	几何学

要使用单个查询同时获取已洗掉的类和添加的类，您可以使用以下命令：

With CTE As (
Select Student, Semester, Class, 
       Dense_Rank() Over (Partition by Student Order by Semester) As N
From Tbl
)
Select T.Student, Max(T1.Semester) As Semester, Max(T.Semester) As [Prev Semester], 
        Max(T2.Deleted_Classes) As Deleted_Classes, Max(T3.Added_Classes) As Added_Classes
From (Select Distinct Student, Semester, N From CTE) As T Cross Apply
     (Select Distinct Student, Semester, N From CTE Where Student=T.Student And N=T.N 1) As T1
              Outer Apply (Select Student, Semester, string_agg(Class, ', ') As Deleted_Classes
                           From CTE 
                           Where Student=T.Student          
                                 And N=T.N
                                 And Class Not In (Select Class From CTE Where Student=T.Student And N=T.N 1)
                           Group by Student, Semester) As T2
              Outer Apply (Select Student, Semester, string_agg(Class, ', ') As Added_Classes
                           From CTE 
                           Where Student=T.Student          
                                 And N=T.N 1
                                 And Class Not In (Select Class From CTE Where Student=T.Student And N=T.N)
                           Group by Student, Semester) As T3
Group by T.Student, T.N 1, T.N
Having Max(T2.Deleted_Classes) Is Not Null Or Max(T3.Added_Classes) Is Not Null

db<>在这里摆弄

结果：

学生	学期	上学期	Deleted_Classes	添加_类
爱丽丝	11	10	葡萄牙语	英语、数学
爱丽丝	12	11	几何、数学	生物、地理
鲍勃	12	11	葡萄牙语	数学

uj5u.com热心网友回复：

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;