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