未出現在輸出中的列名上的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

結果：

要使用單個查詢同時獲取已洗掉的類和添加的類，您可以使用以下命令：

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<>在這里擺弄

結果：

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;

您可以從這些值中獲得各種資訊。例如，知道最近的學期不是 12 年意味著該課程被取消了。你可以做類似的添加。

uj5u.com熱心網友回復：

使用 aNOT 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;

select 
  Student
, Semester as [Semester] 
, Semester-1 as [Prev_Semester]
, STRING_AGG(Class, ', ') as [Added_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
    )
group by Student, Semester;

合并，使用全連接

select 
  coalesce(t1.Student, t2.Student) as Student
, coalesce(t2.Semester, t1.Semester 1) as [Semester] 
, coalesce(t1.Semester, t2.Semester-1) as [Prev_Semester]
, STRING_AGG(t1.Class, ', ') as [Deleted_Classes] 
, STRING_AGG(t2.Class, ', ') as [Added_Classes]  
from StudentSemesters t1
full join StudentSemesters t2
  on t2.Student = t1.Student
 and t2.Class = t1.Class
 and t2.Semester = t1.Semester 1
where (t1.Class is null or
 (t2.Class is null
  and exists (
    select 1
    from StudentSemesters t3
    where t3.Student = t1.Student
      and t3.Semester = t1.Semester 1
    )))
group by 
  coalesce(t1.Student, t2.Student), 
  coalesce(t2.Semester, t1.Semester 1), 
  coalesce(t1.Semester, t2.Semester-1) 
order by Student, Semester;

關于db<>fiddle 的演示在這里

標籤：sql sql-server 查询语句

上一篇：查詢洗掉90天前100-100個批次的資料

下一篇：如何求和來自同一個選擇陳述句的另一個總和的值