# SQL 為何抽取不同部份學生的成績

1. ### tr-wyyu Expand Collapse

文章:
17
讚:
0
想抽取全校學生過往的六年成績。但部份班別抽取不同資料，但全部已完成數據整合，而檢查過有輸入積分的。

求教解決方法。

select A.*, B.學段一 '20學段一' , B.學段二 '20學段二', B.學段三 '20學段三' from (
select A.*, B.學段一 '19學段一' , B.學段二 '19學段二', B.學段三 '19學段三' from (
select A.*, B.學段一 '18學段一' , B.學段二 '18學段二', B.學段三 '18學段三' from (
select A.*, B.學段一 '17學段一' , B.學段二 '17學段二', B.學段三 '17學段三' from (
select A.*, B.學段一 '16學段一' , B.學段二 '16學段二', B.學段三 '16學段三' from (
select A.*, B.學段一 '15學段一' , B.學段二 '15學段二', B.學段三 '15學段三' from (

select a.STUID, a.REGNO, b.SCHYEAR , b.CLASSCODE, b.CLASSNO, a.CHNAME from TB_STU_STUDENT a,VW_STU_CLASSREG b where a.STUID = b.STUID and b.SCHYEAR = '2020')

A LEFT OUTER JOIN
(select a.STUID, a.SUBJCODE, a.SYSSCORE '學段一', b.SYSSCORE '學段二', c.SYSSCORE '學段三' from TB_ASR_SubjAssessData a, TB_ASR_SubjAssessData b, TB_ASR_SubjAssessData c where a.STUID = b.STUID AND a.STUID = c.STUID AND a.SCHYEAR = '2015' AND b.SCHYEAR = '2015' AND c.SCHYEAR = '2015' AND a.TIMESEQ = '1100' AND b.TIMESEQ = '1200' AND c.TIMESEQ = '1300' AND a.SUBJCODE = '080' AND b.SUBJCODE = '080' AND c.SUBJCODE = '080') B ON A.STUID = B.STUID)

A LEFT OUTER JOIN
(select a.STUID, a.SUBJCODE, a.SYSSCORE '學段一', b.SYSSCORE '學段二', c.SYSSCORE '學段三' from TB_ASR_SubjAssessData a, TB_ASR_SubjAssessData b, TB_ASR_SubjAssessData c where a.STUID = b.STUID AND a.STUID = c.STUID AND a.SCHYEAR = '2016' AND b.SCHYEAR = '2016' AND c.SCHYEAR = '2016' AND a.TIMESEQ = '1100' AND b.TIMESEQ = '1200' AND c.TIMESEQ = '1300' AND a.SUBJCODE = '080' AND b.SUBJCODE = '080' AND c.SUBJCODE = '080') B ON A.STUID = B.STUID)

A LEFT OUTER JOIN
(select a.STUID, a.SUBJCODE, a.SYSSCORE '學段一', b.SYSSCORE '學段二', c.SYSSCORE '學段三' from TB_ASR_SubjAssessData a, TB_ASR_SubjAssessData b, TB_ASR_SubjAssessData c where a.STUID = b.STUID AND a.STUID = c.STUID AND a.SCHYEAR = '2017' AND b.SCHYEAR = '2017' AND c.SCHYEAR = '2017' AND a.TIMESEQ = '1100' AND b.TIMESEQ = '1200' AND c.TIMESEQ = '1300' AND a.SUBJCODE = '080' AND b.SUBJCODE = '080' AND c.SUBJCODE = '080') B ON A.STUID = B.STUID)

A LEFT OUTER JOIN
(select a.STUID, a.SUBJCODE, a.SYSSCORE '學段一', b.SYSSCORE '學段二', c.SYSSCORE '學段三' from TB_ASR_SubjAssessData a, TB_ASR_SubjAssessData b, TB_ASR_SubjAssessData c where a.STUID = b.STUID AND a.STUID = c.STUID AND a.SCHYEAR = '2018' AND b.SCHYEAR = '2018' AND c.SCHYEAR = '2018' AND a.TIMESEQ = '1100' AND b.TIMESEQ = '1200' AND c.TIMESEQ = '1300' AND a.SUBJCODE = '080' AND b.SUBJCODE = '080' AND c.SUBJCODE = '080') B ON A.STUID = B.STUID)

A LEFT OUTER JOIN
(select a.STUID, a.SUBJCODE, a.SYSSCORE '學段一', b.SYSSCORE '學段二', c.SYSSCORE '學段三' from TB_ASR_SubjAssessData a, TB_ASR_SubjAssessData b, TB_ASR_SubjAssessData c where a.STUID = b.STUID AND a.STUID = c.STUID AND a.SCHYEAR = '2019' AND b.SCHYEAR = '2019' AND c.SCHYEAR = '2019' AND a.TIMESEQ = '1100' AND b.TIMESEQ = '1200' AND c.TIMESEQ = '1300' AND a.SUBJCODE = '080' AND b.SUBJCODE = '080' AND c.SUBJCODE = '080') B ON A.STUID = B.STUID)

A LEFT OUTER JOIN
(select a.STUID, a.SUBJCODE, a.SYSSCORE '學段一', b.SYSSCORE '學段二', c.SYSSCORE '學段三' from TB_ASR_SubjAssessData a, TB_ASR_SubjAssessData b, TB_ASR_SubjAssessData c where a.STUID = b.STUID AND a.STUID = c.STUID AND a.SCHYEAR = '2020' AND b.SCHYEAR = '2020' AND c.SCHYEAR = '2020' AND a.TIMESEQ = '1100' AND b.TIMESEQ = '1200' AND c.TIMESEQ = '1300' AND a.SUBJCODE = '080' AND b.SUBJCODE = '080' AND c.SUBJCODE = '080') B ON A.STUID = B.STUID

ORDER BY A.CLASSCODE,A.CLASSNO

#1 tr-wyyu, 2021-05-13
2. ### edb-catherinewschan Expand Collapse

文章:
191
讚:
0
#2 edb-catherinewschan, 2021-05-17