SQL : select data with null value on end date if other record has the same start date

Question

我目前正在使用 SQL Server 2008。我有多个记录让学生注册了同一门类(class)，但有些记录具有相同的开始日期和空值或结束日期的实际日期。如果同一学生的开始日期相同，我将尝试获取具有空值的记录。系统中的数据还包含有开始和结束日期的记录，但我打算保留这些记录。我尝试了下面的查询，但没有返回任何结果。有没有办法做到这一点？任何帮助，将不胜感激。

SELECT DISTINCT
  t1.*
FROM Enrolled_students t1
JOIN Enrolled_students t2
  ON t1.studentid = t2.studentid
  AND t1.program_enrolled = t2.program_enrolled
  AND t1.startdate = t2.stardate
  AND t1.enddate <> t2.enddate

数据

StudentID    program    StartDate            enddate_Date
267342      Science   2016-09-19 00:00:00.000    NULL
267342      science   2016-09-19 00:00:00.000   2017-01-17 00:00:00.000 
435359      math      2017-05-18 00:00:00.000   2017-08-29 00:00:00.000
290332      Lab       2014-02-11 00:00:00.000   NULL 

结果

StudentID    program    startDate            end_Date
267342      Science   2016-09-19 00:00:00.000    NULL
435359      math      2017-05-18 00:00:00.000   2017-08-29 00:00:00.000
290332      Lab       2014-02-11 00:00:00.000   NULL 

Answer 1

运行以下 SQL。这将返回带有结束日期的学生记录，该记录在同一个表中有另一个条目，用于相同的学生+类(class)+开始日期组合，结束日期为 NULL

SELECT
*
FROM Enrolled_students 
    WHERE enddate_Date IS NOT NULL
        AND EXISTS
        (
            SELECT
                1
                FROM Enrolled_students  ES
                    WHERE ES.StudentID = Enrolled_students.StudentID
                        AND ES.Program = Enrolled_students.Program
                        AND ES.StartDate = Enrolled_students.StartDate
                        AND ES.enddate_Date IS NULL
        )