SQL 抽取學生缺席紀錄問題

我們在使用資料管理模組時遇到了問題，在學生出席資料內我們輸入了的資料，在學生出席資料模組內檢過，没有問題，但當到資料管理模組做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為空格。

 

你好,

不用 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