想加埋 最後出席日期 SELECT A.REGNO, A.SCHLVL, B.EN_DES, B.CH_DES, A.CLASSNO, D.CLASSNAME, A.SUBJGROUP, A.ENNAME, A.CHNAME, A.SEX, A.CCC, DATEFORMAT(A.DOB, 'DD/MM/YYYY') AS DOB, A.STRN, A.BIRTHCERT, A.HKID, A.SCHHOUSE,e.ch_des 'Status' FROM VW_STU_LATESTSTUDENT A LEFT OUTER JOIN TB_HSE_COMMON B ON B.SUID = A.SUID AND B.TB_ID = 'SCHLVL' AND B.CODE_ID = A.SCHLVL LEFT OUTER JOIN TB_HSE_COMMON C ON C.SUID = A.SUID AND C.TB_ID = 'SESSIO' AND C.CODE_ID = A.SCHSESS LEFT OUTER JOIN TB_SCH_SCHCLASS D ON A.SUID = D.SUID AND A.SCHYEAR = D.SCHYEAR AND A.SCHLVL = D.SCHLEVEL AND A.SCHSESS = D.SCHSESSION AND A.CLASSLVL = D.CLASSLEVEL AND A.CLASSCODE = D.CLASSCODE left outer join tb_hse_common e on a.suid=e.suid and a.status=e.code_id and e.tb_id='STUDST' WHERE A.SCHYEAR = ? ORDER BY D.CLASSNAME, A.CLASSNO
你好, 可以參考紅色的部份 SELECT A.REGNO, A.SCHLVL, B.EN_DES, B.CH_DES, A.CLASSNO, D.CLASSNAME, A.SUBJGROUP, A.ENNAME, A.CHNAME, A.SEX, A.CCC, DATEFORMAT(A.DOB, 'DD/MM/YYYY') AS DOB, A.STRN, A.BIRTHCERT, A.HKID, A.SCHHOUSE,e.ch_des 'Status', dateformat(F.LASTATTEND,'DD/MM/YYYY') 'LASTATTEND_DATE' FROM wsadmin.VW_STU_LATESTSTUDENT A LEFT OUTER JOIN wsadmin.TB_HSE_COMMON B ON B.SUID = A.SUID AND B.TB_ID = 'SCHLVL' AND B.CODE_ID = A.SCHLVL LEFT OUTER JOIN wsadmin.TB_HSE_COMMON C ON C.SUID = A.SUID AND C.TB_ID = 'SESSIO' AND C.CODE_ID = A.SCHSESS LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS D ON A.SUID = D.SUID AND A.SCHYEAR = D.SCHYEAR AND A.SCHLVL = D.SCHLEVEL AND A.SCHSESS = D.SCHSESSION AND A.CLASSLVL = D.CLASSLEVEL AND A.CLASSCODE = D.CLASSCODE left outer join wsadmin.tb_hse_common e on a.suid=e.suid and a.status=e.code_id and e.tb_id='STUDST' LEFT OUTER JOIN wsadmin.TB_STU_DEPARTURE F on A.suid=F.suid and A.stuid=F.stuid WHERE A.SCHYEAR = ? ORDER BY D.CLASSNAME, A.CLASSNO