其他 - 使用Oracle SQL将重复计数连接到现有子查询

我被要求构建一个查询，该查询针对某个状态测试对数据进行质量检查。

我有以下查询，该查询创建一个名为“ Flag”的变量以识别出现问题的情况。主要查询实际上是在子查询中，并且我仅通过选择Flag具有值的情况来过滤掉没有Flag的所有记录。

对于有重复测试的实例，我希望能够有一个CASE WHEN。我们确实有一些重复项，其中对于同一学生ID（test.studentID，studenttestscore.studentID，students.ID），同一学生的同一考试日期将相同。

如您所见，测试日期是STUDENTTEST.TEST_DATE。考试可以通过STUDENTTEST.ID或TEST.NAME进行标识。查找STUDENTTEST.studentID的STUDENTTEST.test_date和STUDENTTEST.ID相同的记录可能会更容易。但是一旦获得该计数，就必须将其附加到此处的子查询中，以便我们可以为其指定一个学生姓名。

with query as (select STUDENTS.STUDENT_NUMBER as "Student ID", Students.Lastfirst as "Student Name",
    STUDENTS.GRADE_LEVEL as "Current Grade Level",
    TEST.NAME as "Test Name",
    STUDENTTEST.GRADE_LEVEL as "Grade level at time of exam",
    STUDENTTEST.TEST_DATE as TEST_DATE,
    STUDENTTESTSCORE.NUMSCORE as Score, STUDENTTESTSCORE.PERCENTSCORE as PCTScore,
S_NY_STU_X.DATEOFENTRYGRade9 as Cohort,
CASE
WHEN STUDENTTEST.GRADE_LEVEL <9 or STUDENTTEST.GRADE_LEVEL>12 then 'Invalid Grade Level'
WHEN STUDENTTESTSCORE.NUMSCORE != STUDENTTESTSCORE.PERCENTSCORE then 'Mismatch between number and percent score'
WHEN TEST.NAME like '% - T%' then 'Transitional Global no longer administered'
WHEN STUDENTTEST.TEST_DATE>CURRENT_DATE THEN 'Test date invalid'
WHEN STUDENTTESTSCORE.NUMSCORE <0 THEN 'Test score out of range'
WHEN STUDENTTESTSCORE.NUMSCORE <0 THEN 'Test score out of range'
WHEN STUDENTTESTSCORE.NUMSCORE >100  THEN 'Test score out of range'
WHEN STUDENTTESTSCORE.PERCENTSCORE > 100 THEN 'Test score out of range'
END AS Flag
 from STUDENTS STUDENTS,
    STUDENTTEST STUDENTTEST,
    STUDENTTESTSCORE STUDENTTESTSCORE,
    TEST TEST,
    TESTSCORE TESTSCORE,
    S_NY_STU_X S_NY_STU_X

 where 
 Test.Name like 'Regents%' and
 STUDENTTESTSCORE.TESTSCOREID=TESTSCORE.ID
    and STUDENTTESTSCORE.STUDENTTESTID=STUDENTTEST.ID
    and STUDENTTESTSCORE.STUDENTID=STUDENTTEST.STUDENTID
    and STUDENTS.ID=STUDENTTEST.STUDENTID
    and STUDENTS.ID=STUDENTTESTSCORE.STUDENTID
    and STUDENTTEST.TESTID=TEST.ID
and S_NY_STU_X.STUDENTSDCID=STUDENTS.DCID)
select * from query where flag is not null 

非常感谢！！