我需要幫助找出誰在考試中失敗并僅通過考試并通過考試，
繼承人的代碼：
select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD,s.NAME , c.NAME as Course_name, EXAM_DT,
case
when SCORE>=PASS_THRESHOLD then 'PASS'
else 'Fail'
end as Flag
from exam_submission es
left join student s on es.STUDENT_ID = s.ID
left join exam e on es.EXAM_ID = e.ID
left join course c on e.COURSE_ID = c.ID
結果如下：
STUDENT_ID EXAM_ID SCORE PASS_THRESHOLD NAME Course_name EXAM_DT Flag
1 3 88 65 Anthony Data Mining 2019-12-17 PASS
1 5 71 70 Anthony Statistic 2019-12-19 PASS
2 1 53 55 Sisca Machine Learning2019-12-17 Fail
2 3 77 65 Sisca Data Mining 2019-12-17 PASS
2 4 85 63 Sisca Data Science 2019-12-18 PASS
2 1 60 55 Sisca Machine Learning2020-01-08 PASS
我需要這樣找到：
2 1 53 55 Sisca Machine Learning2019-12-17 Fail
2 1 60 55 Sisca Machine Learning2020-01-08 PASS
uj5u.com熱心網友回復：
可能使用如下查詢。這是使用您的查詢作為輸入。此外，我們假設不可能讓學生在兩年內按時間順序參加同一考試（通過，失敗）。
; with inputdata as
(
select STUDENT_ID,EXAM_ID,SCORE,PASS_THRESHOLD,s.NAME , c.NAME as Course_name, EXAM_DT,
case
when SCORE>=PASS_THRESHOLD then 'PASS'
else 'Fail'
end as Flag
from exam_submission es
left join student s on es.STUDENT_ID = s.ID
left join exam e on es.EXAM_ID = e.ID
left join course c on e.COURSE_ID = c.ID
)
select * from Inputdata I
join
( select student_id, exam_id from
inputdata
group by student_id, exam_id
having count(distinct flag)=2
)T on I.student_id=T.student_id and I.exam_id=T.exam_id
order by exam_dt asc
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/460343.html