負責SQL之同工, 你好,我校年中使用此Sql抽取資料予班主任評學生操行。今年應校方安求,使用了獎懲資料內的「後置補充」功能。如代碼文字(熱心服務) / 後置補充 (一百五十周年校慶陸運會)。現時只能抽取(熱心服務),請問如何優化為抽出﹕(熱心服務) + (一百五十周年校慶陸運會) 謝謝及附上原本之Sql如下﹕ Select Distinct b.Schyear, '#'||b.REGNO "Regno", b.CLASSCODE 'Class', b.CLASSNO 'No', b.ENNAME 'EName', b.CHNAME 'Name', DATEFORMAT(a.ANPDATE, 'DD/MM/YYYY') 'DATE', g.CH_DES 'Prize', TSSD.CH_DES 'Activity', TSSE.CH_DES 'Post', h.CH_DES 'Punish', a.LVL3*9+a.LVL2*3+a.LVL1 'Lvl' FROM VW_STU_ANPSTUREC a JOIN VW_STU_LATESTSTUDENT b ON b.SUID = a.SUID AND b.STUID = a.STUID AND b.SCHYEAR = a.SCHYEAR left outer join vw_stu_lateststudent c1 on c1.suid=b.suid and c1.stuid=b.stuid and c1.SCHYEAR=b.SCHYEAR LEFT OUTER JOIN TB_SCH_SCHOOL c ON c.SUID = a.SUID AND c.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN TB_STU_ANPSCHEMEDETAILMD d ON d.SUID = a.SUID AND d.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN TB_STU_ANPSCHEME e ON e.SUID = a.SUID AND e.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN TB_ATT_NONATT f ON f.SUID = a.SUID AND f.STUID = a.STUID AND f.ANPRECID = a.ANPRECID LEFT OUTER JOIN TB_HSE_COMMON g ON g.SUID = a.SUID AND g.CODE_ID = a.DESCRIPTIONCODE AND g.TB_ID = 'PRIZE' and a.ANPTYPE='A' LEFT OUTER JOIN TB_HSE_COMMON h ON h.SUID = a.SUID AND h.CODE_ID = a. DESCRIPTIONCODE AND h.TB_ID = 'PSHMNT' and a.ANPTYPE='P' LEFT OUTER JOIN VW_STU_LATESTSTUSCHREC i ON i.SUID = a.SUID AND i.STUID = a.STUID AND i.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN TB_SCH_SCHCLASS j ON j.SUID = i.SUID AND j.SCHYEAR = i.SCHYEAR AND j.SCHLEVEL = i.SCHLVL AND j.SCHSESSION = i.SCHSESS AND j.CLASSLEVEL = i.CLASSLVL AND j.CLASSCODE = i.CLASSCODE LEFT OUTER JOIN VW_NAA_CLASSNAA s ON a.suid = s.suid AND a.STUID = s.STUID AND a.schyear = s.schyear and a.NAAOFFEREDID=s.NAAOFFEREDID LEFT OUTER JOIN TB_SCH_SCHCLASS TSSA ON TSSA.SUID = S.SUID AND TSSA.SCHYEAR = S.SCHYEAR AND TSSA.SCHLEVEL = S.SCHLVL AND TSSA.SCHSESSION = S.SCHSESS AND TSSA.CLASSLEVEL = S.CLASSLVL AND TSSA.CLASSCODE = S.CLASSCODE LEFT OUTER JOIN TB_SCH_SCHOOL TSSB ON TSSB.SUID = A.SUID AND TSSB.SCHYEAR = A.SCHYEAR LEFT OUTER JOIN VW_NAA_NAATYPEDURATION TSSC ON TSSC.SUID = S.SUID AND TSSC.NAATYPE = S.NAATYPE AND TSSC.NAADURATION = S.DURATION and (TSSC.NAATYPE = 'E' or TSSC.NAATYPE = 'S') LEFT OUTER JOIN TB_HSE_COMMON TSSD ON TSSD.SUID = S.SUID AND TSSD.CODE_ID = S.NAACODE and (TSSD.TB_ID = 'ECACD' or TSSD.TB_ID = 'SEVPST') and TSSD.CODE_ST ='A' LEFT OUTER JOIN TB_HSE_COMMON TSSE ON TSSE.SUID = S.SUID AND TSSE.CODE_ID = S.NAAPOSTCODE and TSSE.TB_ID = 'ECAPST' WHERE a.SCHYEAR = 2021 ORDER BY b.CLASSCODE, b.CLASSNO
你好, 加了 '前置補充' 和 '後置補充' ,請試試看 Select Distinct b.Schyear, '#'||b.REGNO "Regno", b.CLASSCODE 'Class', b.CLASSNO 'No', b.ENNAME 'EName', b.CHNAME 'Name', DATEFORMAT(a.ANPDATE, 'DD/MM/YYYY') 'DATE', a.prefix '前置補充', g.CH_DES 'Prize', a.POSITION '後置補充', TSSD.CH_DES 'Activity', TSSE.CH_DES 'Post', h.CH_DES 'Punish', a.LVL3*9+a.LVL2*3+a.LVL1 'Lvl' FROM wsadmin.VW_STU_ANPSTUREC a JOIN wsadmin.VW_STU_LATESTSTUDENT b ON b.SUID = a.SUID AND b.STUID = a.STUID AND b.SCHYEAR = a.SCHYEAR left outer join wsadmin.vw_stu_lateststudent c1 on c1.suid=b.suid and c1.stuid=b.stuid and c1.SCHYEAR=b.SCHYEAR LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL c ON c.SUID = a.SUID AND c.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEMEDETAILMD d ON d.SUID = a.SUID AND d.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEME e ON e.SUID = a.SUID AND e.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_ATT_NONATT f ON f.SUID = a.SUID AND f.STUID = a.STUID AND f.ANPRECID = a.ANPRECID LEFT OUTER JOIN wsadmin.TB_HSE_COMMON g ON g.SUID = a.SUID AND g.CODE_ID = a.DESCRIPTIONCODE AND g.TB_ID = 'PRIZE' and a.ANPTYPE='A' LEFT OUTER JOIN wsadmin.TB_HSE_COMMON h ON h.SUID = a.SUID AND h.CODE_ID = a. DESCRIPTIONCODE AND h.TB_ID = 'PSHMNT' and a.ANPTYPE='P' LEFT OUTER JOIN wsadmin.VW_STU_LATESTSTUSCHREC i ON i.SUID = a.SUID AND i.STUID = a.STUID AND i.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS j ON j.SUID = i.SUID AND j.SCHYEAR = i.SCHYEAR AND j.SCHLEVEL = i.SCHLVL AND j.SCHSESSION = i.SCHSESS AND j.CLASSLEVEL = i.CLASSLVL AND j.CLASSCODE = i.CLASSCODE LEFT OUTER JOIN wsadmin.VW_NAA_CLASSNAA s ON a.suid = s.suid AND a.STUID = s.STUID AND a.schyear = s.schyear and a.NAAOFFEREDID=s.NAAOFFEREDID LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS TSSA ON TSSA.SUID = S.SUID AND TSSA.SCHYEAR = S.SCHYEAR AND TSSA.SCHLEVEL = S.SCHLVL AND TSSA.SCHSESSION = S.SCHSESS AND TSSA.CLASSLEVEL = S.CLASSLVL AND TSSA.CLASSCODE = S.CLASSCODE LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL TSSB ON TSSB.SUID = A.SUID AND TSSB.SCHYEAR = A.SCHYEAR LEFT OUTER JOIN wsadmin.VW_NAA_NAATYPEDURATION TSSC ON TSSC.SUID = S.SUID AND TSSC.NAATYPE = S.NAATYPE AND TSSC.NAADURATION = S.DURATION and (TSSC.NAATYPE = 'E' or TSSC.NAATYPE = 'S') LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSD ON TSSD.SUID = S.SUID AND TSSD.CODE_ID = S.NAACODE and (TSSD.TB_ID = 'ECACD' or TSSD.TB_ID = 'SEVPST') and TSSD.CODE_ST ='A' LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSE ON TSSE.SUID = S.SUID AND TSSE.CODE_ID = S.NAAPOSTCODE and TSSE.TB_ID = 'ECAPST' WHERE a.SCHYEAR = ? ORDER BY b.CLASSCODE, b.CLASSNO
感謝edb-catherinewschan快捷的回覆 發覺此sql抽出來的是「事件(英文)」內之「前置/後置補充」資料 而早前同事輸入的是「事件(中文)」內之「前置/後置補充」資料 請問如何可抽出事件(中文)之補充資料 謝謝!
你好, 已加入中文的前置/後置補充, 請看看 Select Distinct b.Schyear, '#'||b.REGNO "Regno", b.CLASSCODE 'Class', b.CLASSNO 'No', b.ENNAME 'EName', b.CHNAME 'Name', DATEFORMAT(a.ANPDATE, 'DD/MM/YYYY') 'DATE', a.prefix '前置補充Eng', oth_antecedence '前置補充Chi', g.CH_DES 'Prize', a.POSITION '後置補充Eng', oth_addendum '後置補充Chi', TSSD.CH_DES 'Activity', TSSE.CH_DES 'Post', h.CH_DES 'Punish', a.LVL3*9+a.LVL2*3+a.LVL1 'Lvl' FROM wsadmin.VW_STU_ANPSTUREC a JOIN wsadmin.VW_STU_LATESTSTUDENT b ON b.SUID = a.SUID AND b.STUID = a.STUID AND b.SCHYEAR = a.SCHYEAR left outer join wsadmin.vw_stu_lateststudent c1 on c1.suid=b.suid and c1.stuid=b.stuid and c1.SCHYEAR=b.SCHYEAR LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL c ON c.SUID = a.SUID AND c.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEMEDETAILMD d ON d.SUID = a.SUID AND d.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEME e ON e.SUID = a.SUID AND e.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_ATT_NONATT f ON f.SUID = a.SUID AND f.STUID = a.STUID AND f.ANPRECID = a.ANPRECID LEFT OUTER JOIN wsadmin.TB_HSE_COMMON g ON g.SUID = a.SUID AND g.CODE_ID = a.DESCRIPTIONCODE AND g.TB_ID = 'PRIZE' and a.ANPTYPE='A' LEFT OUTER JOIN wsadmin.TB_HSE_COMMON h ON h.SUID = a.SUID AND h.CODE_ID = a. DESCRIPTIONCODE AND h.TB_ID = 'PSHMNT' and a.ANPTYPE='P' LEFT OUTER JOIN wsadmin.VW_STU_LATESTSTUSCHREC i ON i.SUID = a.SUID AND i.STUID = a.STUID AND i.SCHYEAR = a.SCHYEAR LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS j ON j.SUID = i.SUID AND j.SCHYEAR = i.SCHYEAR AND j.SCHLEVEL = i.SCHLVL AND j.SCHSESSION = i.SCHSESS AND j.CLASSLEVEL = i.CLASSLVL AND j.CLASSCODE = i.CLASSCODE LEFT OUTER JOIN wsadmin.VW_NAA_CLASSNAA s ON a.suid = s.suid AND a.STUID = s.STUID AND a.schyear = s.schyear and a.NAAOFFEREDID=s.NAAOFFEREDID LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS TSSA ON TSSA.SUID = S.SUID AND TSSA.SCHYEAR = S.SCHYEAR AND TSSA.SCHLEVEL = S.SCHLVL AND TSSA.SCHSESSION = S.SCHSESS AND TSSA.CLASSLEVEL = S.CLASSLVL AND TSSA.CLASSCODE = S.CLASSCODE LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL TSSB ON TSSB.SUID = A.SUID AND TSSB.SCHYEAR = A.SCHYEAR LEFT OUTER JOIN wsadmin.VW_NAA_NAATYPEDURATION TSSC ON TSSC.SUID = S.SUID AND TSSC.NAATYPE = S.NAATYPE AND TSSC.NAADURATION = S.DURATION and (TSSC.NAATYPE = 'E' or TSSC.NAATYPE = 'S') LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSD ON TSSD.SUID = S.SUID AND TSSD.CODE_ID = S.NAACODE and (TSSD.TB_ID = 'ECACD' or TSSD.TB_ID = 'SEVPST') and TSSD.CODE_ST ='A' LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSE ON TSSE.SUID = S.SUID AND TSSE.CODE_ID = S.NAAPOSTCODE and TSSE.TB_ID = 'ECAPST' WHERE a.SCHYEAR = ? ORDER BY b.CLASSCODE, b.CLASSNO