问题

https://stackoverflow.com/questions/70510156/inner-join-of-an-except-subquery-on-column-names-that-do-not-appear-in-the-outpu

I have a table of students and classes. I would like to find what classes were dropped from one semester to another (and a similar query for classes added).

Student Class Semester

==============================

Alice English 11

Alice Geometry 11

Alice English 12

Bob Spanish 11

Bob Spanish 12

My approach is to use anexcept(same asminus):

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

This works correctly, returningGeometry. However, I need to use this as a subquery like this:

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

My problems is with the____section - inner joins can only be joined on columns that appear in the output. But myexceptquery doesn't return the values of previous and current semesters (it might even return nothing at all if no classes were dropped). So how do I join the subquery into the main table? My desired output is:

Student Semester Prev Semester Deleted_Classes

========================================================

Alice 12 11 Geometry

Aliceappears because she had a change in her schedule, butBobis omitted because there was no change in his schedule.

解答

对Student、Semester 有序的数据，先按 Student 分组，组内再按 Semester 分组，组内进行行间计算，下学期和上学期的 Class 集合的差集，就是新增的，反之就是取消的。这类运算用SQL写起来很麻烦，需要借助窗口函数、CROSSAPPLY和OUTERAPPLY完成计算，写出的SQL冗长难读。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要两句：

SPL源代码：https://github.com/SPLWare/esProc

问答搜集