請問如何修改以下SQL 以顯示同一科目不同組別的成績? 原本SQL: select a.classcode 'Class', a.classno 'No', a.chname 'Name(Chi)', a.enname 'Name(Eng)', i.sysscore 'Chin', n.sysscore 'Chis', f.syspercscore 'Score', f.omclass 'OMC', f.omclasslvl 'OMF' from vw_stu_lateststudent a left outer join tb_asr_studassessdata f on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.TIMESEQ=? left outer join wsadmin.TB_ASR_SUBJASSESSDATA i on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=f.TIMESEQ left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='075' and n.TIMESEQ=f.TIMESEQ where a.schyear=? and a.classlvl=? order by a.classcode, a.classno, a.enname 我在WEBSAMS有以下設定: School Management-->Cross-class subject 1) Group Type: Non-Subject Block Group Code: C1 Group Name: S1 Chinese Subject: Chinese Language Sub-Group: C_A, C_B, C_C, C_CD, C_D 2) Group Type: Subject Block Group Code: 22CH Group Name: Chinese history Subject: Chinese History Sub-Group: CH_X1, CH_X3 原本結果: Class, No, Name(Chi), Name (Eng), Chin, Chis, Score, OMC, OMF 可以怎樣修改成這樣的結果呢? Class, No, Name(Chi), Name(Eng), Chin, Chin_A, Chin_B, Chin_D, Chin_CD, Chis, Chis_X1, Chis_X2, Score, OMC, OMF 謝謝你!
你好 可以參考之前的貼文，都是關於加插跨班別科目的資料。 https://forum.hkedcity.net/index.php?threads/sql中加入跨班分組組別名稱.180845/#post-419477 根據你的情況，我也嘗試一下更改... .. 加插跨班別科目的分組作為欄位，看看是否合適。由於加一個欄位，必須連結一次資料表，所以JOINING比較多。 select a.classcode 'Class', a.classno 'No', a.chname 'Name(Chi)', a.enname 'Name(Eng)', i.sysscore 'Chin', (case when d.SUBJGROUP is null then trim(cast(d.CROSSCLSGRP as char)+' '+cast(d.CROSSCLSSUBGRP as char)) else d.SUBJGROUP end) as 'Chin_A', (case when e.SUBJGROUP is null then trim(cast(e.CROSSCLSGRP as char)+' '+cast(e.CROSSCLSSUBGRP as char)) else e.SUBJGROUP end) as 'Chin_B', (case when f.SUBJGROUP is null then trim(cast(f.CROSSCLSGRP as char)+' '+cast(f.CROSSCLSSUBGRP as char)) else f.SUBJGROUP end) as 'Chin_C', (case when g.SUBJGROUP is null then trim(cast(g.CROSSCLSGRP as char)+' '+cast(g.CROSSCLSSUBGRP as char)) else g.SUBJGROUP end) as 'Chin_D', (case when h.SUBJGROUP is null then trim(cast(h.CROSSCLSGRP as char)+' '+cast(h.CROSSCLSSUBGRP as char)) else h.SUBJGROUP end) as 'Chin_CD', n.sysscore 'Chis', (case when o.SUBJGROUP is null then trim(cast(o.CROSSCLSGRP as char)+' '+cast(o.CROSSCLSSUBGRP as char)) else o.SUBJGROUP end) as 'Chis_X1', (case when p.SUBJGROUP is null then trim(cast(p.CROSSCLSGRP as char)+' '+cast(p.CROSSCLSSUBGRP as char)) else p.SUBJGROUP end) as 'Chis_X2', b.syspercscore 'Score', b.omclass 'OMC', b.omclasslvl 'OMF' from wsadmin.vw_stu_lateststudent a left outer join wsadmin.tb_asr_studassessdata b on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.TIMESEQ=? left outer join wsadmin.TB_ASR_SUBJASSESSDATA i on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=b.TIMESEQ left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME d on d.suid=i.suid and d.schlevel=i.schlevel and d.schsession=i.schsession and d.classlevel=i.classlevel and d.schyear=i.schyear and d.stuid=i.stuid and d.timeseq=i.timeseq and d.subjcode=i.subjcode and d.SUBJCOMP is null and d.CROSSCLSGRP='C1' and d.CROSSCLSSUBGRP='C_A' left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME e on e.suid=i.suid and e.schlevel=i.schlevel and e.schsession=i.schsession and e.classlevel=i.classlevel and e.schyear=i.schyear and e.stuid=i.stuid and e.timeseq=i.timeseq and e.subjcode=i.subjcode and e.SUBJCOMP is null and e.CROSSCLSGRP='C1' and e.CROSSCLSSUBGRP='C_B' left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME f on f.suid=i.suid and f.schlevel=i.schlevel and f.schsession=i.schsession and f.classlevel=i.classlevel and f.schyear=i.schyear and f.stuid=i.stuid and f.timeseq=i.timeseq and f.subjcode=i.subjcode and f.SUBJCOMP is null and f.CROSSCLSGRP='C1' and f.CROSSCLSSUBGRP='C_C' left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME g on g.suid=i.suid and g.schlevel=i.schlevel and g.schsession=i.schsession and g.classlevel=i.classlevel and g.schyear=i.schyear and g.stuid=i.stuid and g.timeseq=i.timeseq and g.subjcode=i.subjcode and g.SUBJCOMP is null and g.CROSSCLSGRP='C1' and g.CROSSCLSSUBGRP='C_CD' left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME h on h.suid=i.suid and h.schlevel=i.schlevel and h.schsession=i.schsession and h.classlevel=i.classlevel and h.schyear=i.schyear and h.stuid=i.stuid and h.timeseq=i.timeseq and h.subjcode=i.subjcode and h.SUBJCOMP is null and h.CROSSCLSGRP='C1' and h.CROSSCLSSUBGRP='C_D' left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='075' and n.TIMESEQ=b.TIMESEQ left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME o on o.suid=n.suid and o.schlevel=n.schlevel and o.schsession=n.schsession and o.classlevel=n.classlevel and o.schyear=n.schyear and o.stuid=n.stuid and o.timeseq=n.timeseq and o.subjcode=n.subjcode and o.SUBJCOMP is null and o.CROSSCLSGRP='22CH' and o.CROSSCLSSUBGRP='CH_X1' left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME p on p.suid=n.suid and p.schlevel=n.schlevel and p.schsession=n.schsession and p.classlevel=n.classlevel and p.schyear=n.schyear and p.stuid=n.stuid and p.timeseq=n.timeseq and p.subjcode=n.subjcode and p.SUBJCOMP is null and p.CROSSCLSGRP='22CH' and p.CROSSCLSSUBGRP='CH_X3' where a.schyear=? and a.classlvl=? order by a.classcode, a.classno, a.enname
另外提供一個顯示方式，比較簡單些。 select a.classcode 'Class', a.classno 'No', a.chname 'Name(Chi)', a.enname 'Name(Eng)', i.sysscore 'Chin', (case when d.SUBJGROUP is null then trim(cast(d.CROSSCLSGRP as char)+' '+cast(d.CROSSCLSSUBGRP as char)) else d.SUBJGROUP end) as 'Chin 分組名稱', n.sysscore 'Chis', (case when e.SUBJGROUP is null then trim(cast(e.CROSSCLSGRP as char)+' '+cast(e.CROSSCLSSUBGRP as char)) else e.SUBJGROUP end) as 'Chis 分組名稱', f.syspercscore 'Score', f.omclass 'OMC', f.omclasslvl 'OMF' from wsadmin.vw_stu_lateststudent a left outer join wsadmin.tb_asr_studassessdata f on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.TIMESEQ=? left outer join wsadmin.TB_ASR_SUBJASSESSDATA i on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=f.TIMESEQ left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME d on d.suid=i.suid and d.schlevel=i.schlevel and d.schsession=i.schsession and d.classlevel=i.classlevel and d.schyear=i.schyear and d.stuid=i.stuid and d.timeseq=i.timeseq and d.subjcode=i.subjcode and d.SUBJCOMP is null left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='075' and n.TIMESEQ=f.TIMESEQ left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME e on e.suid=n.suid and e.schlevel=n.schlevel and e.schsession=n.schsession and e.classlevel=n.classlevel and e.schyear=n.schyear and e.stuid=n.stuid and e.timeseq=n.timeseq and e.subjcode=n.subjcode and e.SUBJCOMP is null where a.schyear=? and a.classlvl=? order by a.classcode, a.classno, a.enname
建議你先檢查一下 學生資料 > 科目設定 > 設定 ，是否每位學生都輸入了 "跨班別科目組別 的 分組" 另外，中文科 和 中史科 有沒有分卷?? 如果資料輸入妥當，但仍然抽取不到資料。 請詢問 學校聯絡主任，將問題轉介給我作進一步了解。
你好， 如果有分卷，便要用另外一個資料表了。 我也嘗試模擬你的情況，輸入了分數後，執行一次數據整合，可以抽取到資料。 select a.STUID, a.classcode 'Class', a.classno 'No', a.chname 'Name(Chi)', a.enname 'Name(Eng)', i.subjcode 'Subject', i.subjcompcode 'Subject Component', i.sysscore 'Chin', (case when d.SUBJGROUP is null then trim(cast(d.CROSSCLSGRP as char)+' '+cast(d.CROSSCLSSUBGRP as char)) else d.SUBJGROUP end) as 'Chin 分組名稱', n.sysscore 'Chis', (case when e.SUBJGROUP is null then trim(cast(e.CROSSCLSGRP as char)+' '+cast(e.CROSSCLSSUBGRP as char)) else e.SUBJGROUP end) as 'Chis 分組名稱', f.syspercscore 'Score', f.omclass 'OMC', f.omclasslvl 'OMF' from wsadmin.vw_stu_lateststudent a left outer join wsadmin.tb_asr_studassessdata f on a.suid=f.suid and a.stuid=f.stuid and a.schyear=f.schyear and f.TIMESEQ=? left outer join wsadmin.TB_ASR_SUBJCOMASSESSDATA i on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=f.TIMESEQ left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME d on d.suid=i.suid and d.schlevel=i.schlevel and d.schsession=i.schsession and d.classlevel=i.classlevel and d.schyear=i.schyear and d.stuid=i.stuid and d.timeseq=i.timeseq and d.subjcode=i.subjcode and d.SUBJCOMP=i.SUBJCOMPCODE left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='075' and n.TIMESEQ=f.TIMESEQ left outer join wsadmin.VW_ASR_CURSTUDSUBJTIME e on e.suid=n.suid and e.schlevel=n.schlevel and e.schsession=n.schsession and e.classlevel=n.classlevel and e.schyear=n.schyear and e.stuid=n.stuid and e.timeseq=n.timeseq and e.subjcode=n.subjcode where a.schyear=? and a.classlvl=? order by a.classcode, a.classno, a.enname 執行結果：
多謝你的回覆! 利用最新的SQL，我成功做到結果，請問中文科可否只顯示1個總分呢? 另外，如果要用第一個顯示方式，即是每個分組用1個Column 顯示，不知道是否可行呢?因為我需要以不同Columns顯示不同分組的分數，如果做到就不需再用Excel Function 將數據顯示到不同的Columns內。 感謝!
如果不出分卷的分數。可以用回TB_ASR_SUBJASSESSDATA資料表，但你要先執行"數據整合" ，再去 "學生成績 > 查詢 > 學生成績 " 查詢一下每位學生是否有中國語文的總分，才執行以下的SQL。 今次用一個簡單的TB_STU_STUSUBJ 資料表來抽取跨班別科目組的資料，由於不包含分卷，所以速度快些。 select a.classcode 'Class', a.classno 'No', a.chname 'Name(Chi)', a.enname 'Name(Eng)', i.sysscore 'Chin', (case when d.CROSSSUBJCODE='C_A' then i.sysscore else null end) as 'Chin_A', (case when e.CROSSSUBJCODE='C_B' then i.sysscore else null end) as 'Chin_B', (case when f.CROSSSUBJCODE='C_C' then i.sysscore else null end) as 'Chin_C', (case when g.CROSSSUBJCODE='C_CD' then i.sysscore else null end) as 'Chin_D', (case when h.CROSSSUBJCODE='C_D' then i.sysscore else null end) as 'Chin_CD', n.sysscore 'Chis', (case when o.CROSSSUBJCODE='CH_X1' then n.sysscore else null end) as 'Chis_X1', (case when p.CROSSSUBJCODE='CH_X3' then n.sysscore else null end) as 'Chis_X3', b.syspercscore 'Score', b.omclass 'OMC', b.omclasslvl 'OMF' from wsadmin.vw_stu_lateststudent a left outer join wsadmin.tb_asr_studassessdata b on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear and b.TIMESEQ=? left outer join wsadmin.TB_ASR_SUBJASSESSDATA i on a.SUID=i.SUID and a.SCHYEAR=i.SCHYEAR and a.STUID=i.STUID and i.SUBJCODE='080' and i.TIMESEQ=b.TIMESEQ left outer join wsadmin.TB_STU_STUSUBJ d on d.suid=i.suid and d.stuid=i.stuid and d.STUSCHRECID=a.STUSCHRECID and d.subjcode=i.subjcode and d.CROSSSUBJGRP='C1' and d.CROSSSUBJCODE='C_A' left outer join wsadmin.TB_STU_STUSUBJ e on e.suid=i.suid and e.stuid=i.stuid and e.STUSCHRECID=a.STUSCHRECID and e.subjcode=i.subjcode and e.CROSSSUBJGRP='C1' and e.CROSSSUBJCODE='C_B' left outer join wsadmin.TB_STU_STUSUBJ f on f.suid=i.suid and f.stuid=i.stuid and f.STUSCHRECID=a.STUSCHRECID and f.subjcode=i.subjcode and f.CROSSSUBJGRP='C1' and f.CROSSSUBJCODE='C_C' left outer join wsadmin.TB_STU_STUSUBJ g on g.suid=i.suid and g.stuid=i.stuid and g.STUSCHRECID=a.STUSCHRECID and g.subjcode=i.subjcode and g.CROSSSUBJGRP='C1' and g.CROSSSUBJCODE='C_CD' left outer join wsadmin.TB_STU_STUSUBJ h on h.suid=i.suid and h.stuid=i.stuid and h.STUSCHRECID=a.STUSCHRECID and h.subjcode=i.subjcode and h.CROSSSUBJGRP='C1' and h.CROSSSUBJCODE='C_D' left outer join wsadmin.TB_ASR_SUBJASSESSDATA n on a.SUID=n.SUID and a.SCHYEAR=n.SCHYEAR and a.STUID=n.STUID and n.SUBJCODE='075' and n.TIMESEQ=b.TIMESEQ left outer join wsadmin.TB_STU_STUSUBJ o on o.suid=n.suid and o.stuid=n.stuid and o.STUSCHRECID=a.STUSCHRECID and o.subjcode=n.subjcode and o.CROSSSUBJGRP='22CH' and o.CROSSSUBJCODE='CH_X1' left outer join wsadmin.TB_STU_STUSUBJ p on p.suid=n.suid and p.stuid=n.stuid and p.STUSCHRECID=a.STUSCHRECID and p.subjcode=n.subjcode and p.CROSSSUBJGRP='22CH' and p.CROSSSUBJCODE='CH_X3' where a.schyear=? and a.classlvl=? order by a.classcode, a.classno, a.enname 執行結果：