SQL 抽取學生缺席紀錄問題

本文由 Ivan Tai 在 2021-02-08 發表於 "WebSAMS 討論區" 討論區

  1. 56736715

    Ivan Tai
    Expand Collapse

    文章:
    1
    讚:
    0
    我們在使用資料管理模組時遇到了問題,在學生出席資料內我們輸入了的資料,在學生出席資料模組內檢過,没有問題,但當到資料管理模組做SQL查詢時,發現部份資料遺留。以下是我們使用的query, 麻煩你看看有什麼問題。

    SELECT VW_ATT_BROKENATT.STUID,
    VW_ATT_BROKENATT.SCHYEAR, TB_STU_STUDENT.REGNO,
    VW_ATT_BROKENATT.CLASSLVL, VW_ATT_BROKENATT.CLASSCODE,
    VW_ATT_BROKENATT.CLASSNO, VW_ATT_BROKENATT.CHNAME,
    VW_ATT_BROKENATT.ENNAME, VW_ATT_BROKENATT.NONATTCODE,
    VW_ATT_BROKENATT.NONATTDATE, VW_ATT_BROKENATT.NONATTSESS,
    VW_ATT_BROKENATT.NONATTTYPE, VW_ATT_BROKENATT.NONATTCOUNT
    FROM TB_STU_STUDENT
    LEFT OUTER JOIN VW_ATT_BROKENATT
    ON TB_STU_STUDENT.STUID= VW_ATT_BROKENATT.STUID,
    VW_ATT_BROKENATT WHERE VW_ATT_BROKENATT.SCHYEAR = 2020
    ORDER BY VW_ATT_BROKENATT.CLASSCODE ASC,
    VW_ATT_BROKENATT.CLASSNO ASC,
    VW_ATT_BROKENATT.NONATTSESS ASC

    謝謝。

    ** 我們嘗試抽取歷年學生資料,發現上述抽取不到資料的學生,他們的SCHLVL 及 SCHSESS為空格。
     
    #1 Ivan Tai, 2021-02-08
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    170
    讚:
    0
    你好,

    不用 TB_STU_STUDENT , 改用 VW_STU_LATESTSTUDENT , 因為
    VW_STU_LATESTSTUDENT包含歷年學生資料。
    另外, 在join VW_ATT_BROKENATT時也要加上schyear

    SELECT
    VW_ATT_BROKENATT.STUID,
    VW_STU_LATESTSTUDENT.SCHLVL,
    VW_STU_LATESTSTUDENT.SCHSESS,
    VW_ATT_BROKENATT.SCHYEAR,
    VW_STU_LATESTSTUDENT.REGNO,
    VW_ATT_BROKENATT.CLASSLVL,
    VW_ATT_BROKENATT.CLASSCODE,
    VW_ATT_BROKENATT.CLASSNO,
    VW_ATT_BROKENATT.CHNAME,
    VW_ATT_BROKENATT.ENNAME,
    VW_ATT_BROKENATT.NONATTCODE,
    VW_ATT_BROKENATT.NONATTDATE,
    VW_ATT_BROKENATT.NONATTSESS,
    VW_ATT_BROKENATT.NONATTTYPE,
    VW_ATT_BROKENATT.NONATTCOUNT
    FROM VW_STU_LATESTSTUDENT
    LEFT OUTER JOIN VW_ATT_BROKENATT ON VW_STU_LATESTSTUDENT.STUID = VW_ATT_BROKENATT.STUID
    AND VW_STU_LATESTSTUDENT.SCHYEAR= VW_ATT_BROKENATT.SCHYEAR WHERE VW_ATT_BROKENATT.SCHYEAR = 2020
    ORDER BY VW_ATT_BROKENATT.CLASSCODE ASC, VW_ATT_BROKENATT.CLASSNO ASC, VW_ATT_BROKENATT.NONATTSESS ASC
     
    #2 edb-catherinewschan, 2021-02-10