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

本文由 tr-wyyu 在 2021-05-13 發表於 "WebSAMS 討論區" 討論區

  1. 55072044

    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. 58521906

    edb-catherinewschan
    Expand Collapse

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