SQL Extract DSE 成績問題

本文由 humphrey 在 2021-06-29 發表於 "WebSAMS 討論區" 討論區

  1. 10000322

    humphrey
    Expand Collapse

    文章:
    28
    讚:
    0
    如何修改以下SQL使Extract DSE 成績時用 6,7 取代5*,5** ?


    select
    a.EXAMCODE,
    a.examyear 'Exam Year',
    a.classcode 'Class',
    a.classno 'Class No.',
    a1.regno 'Registration No.',
    a1.ENNAME 'Name',
    a1.CHNAME 'Chinese Name',
    a1.sex 'Gender',
    b0.subjgrdlvl 'A010 Chinese',
    c0.subjgrdlvl 'A020 English',
    d0.subjgrdlvl 'A030 Math Compulsory',
    e0.subjgrdlvl 'A040 Liberal Studies',
    s.subjgrdlvl 'A050 CLI',
    d.subjgrdlvl 'A070 Chinese History',
    e.subjgrdlvl 'A080 Economics',
    f.subjgrdlvl 'A090 Ethics and Religious Studies',
    g.subjgrdlvl 'A100 Geography',
    h.subjgrdlvl 'A110 History',
    i.subjgrdlvl 'A130 Biology',
    j.subjgrdlvl 'A140 Chemistry',
    k.subjgrdlvl 'A150 Physics',
    m.subjgrdlvl 'A171 BASF',
    n.subjgrdlvl 'A180 Design and Applied Technology',
    o.subjgrdlvl 'A200 ICT',
    p.subjgrdlvl 'A230 Visual Arts',
    q.subjgrdlvl 'A120 TH',
    r.subjgrdlvl 'A190 HMSC',
    c.subjgrdlvl 'A032 M2'
    from tb_hke_dsecandinfo a
    left outer join tb_stu_student a1
    on a.suid=a1.suid and a.stuid=a1.stuid
    left outer join tb_hke_dsesubjresult b0
    on a.suid=b0.suid and a.schcode=b0.schcode and a.candno=b0.candno and a.examyear=b0.examyear and b0.subjcode='A010'
    left outer join tb_hke_dsesubjcompresult b1
    on b0.suid=b1.suid and b0.schcode=b1.schcode and b0.candno=b1.candno and b0.examyear=b1.examyear and b1.subjcode=b0.subjcode and b1.subjcompcode='1'
    left outer join tb_hke_dsesubjcompresult b2
    on b0.suid=b2.suid and b0.schcode=b2.schcode and b0.candno=b2.candno and b0.examyear=b2.examyear and b2.subjcode=b0.subjcode and b2.subjcompcode='2'
    left outer join tb_hke_dsesubjcompresult b3
    on b0.suid=b3.suid and b0.schcode=b3.schcode and b0.candno=b3.candno and b0.examyear=b3.examyear and b3.subjcode=b0.subjcode and b3.subjcompcode='3'
    left outer join tb_hke_dsesubjcompresult b4
    on b0.suid=b4.suid and b0.schcode=b4.schcode and b0.candno=b4.candno and b0.examyear=b4.examyear and b4.subjcode=b0.subjcode and b4.subjcompcode='4'
    left outer join tb_hke_dsesubjresult c0
    on a.suid=c0.suid and a.schcode=c0.schcode and a.candno=c0.candno and a.examyear=c0.examyear and c0.subjcode='A020'
    left outer join tb_hke_dsesubjcompresult c1
    on c0.suid=c1.suid and c0.schcode=c1.schcode and c0.candno=c1.candno and c0.examyear=c1.examyear and c1.subjcode=c0.subjcode and c1.subjcompcode='1'
    left outer join tb_hke_dsesubjcompresult c2
    on c0.suid=c2.suid and c0.schcode=c2.schcode and c0.candno=c2.candno and c0.examyear=c2.examyear and c2.subjcode=c0.subjcode and c2.subjcompcode='2'
    left outer join tb_hke_dsesubjcompresult c3
    on c0.suid=c3.suid and c0.schcode=c3.schcode and c0.candno=c3.candno and c0.examyear=c3.examyear and c3.subjcode=c0.subjcode and c3.subjcompcode='3'
    left outer join tb_hke_dsesubjcompresult c4
    on c0.suid=c4.suid and c0.schcode=c4.schcode and c0.candno=c4.candno and c0.examyear=c4.examyear and c4.subjcode=c0.subjcode and c4.subjcompcode='4'
    left outer join tb_hke_dsesubjresult d0
    on a.suid=d0.suid and a.schcode=d0.schcode and a.candno=d0.candno and a.examyear=d0.examyear and d0.subjcode='A030'
    left outer join tb_hke_dsesubjresult e0
    on a.suid=e0.suid and a.schcode=e0.schcode and a.candno=e0.candno and a.examyear=e0.examyear and e0.subjcode='A040'
    left outer join tb_hke_dsesubjresult b
    on a.suid=b.suid and a.schcode=b.schcode and a.candno=b.candno and a.examyear=b.examyear and b.subjcode='A031'
    left outer join tb_hke_dsesubjresult c
    on a.suid=c.suid and a.schcode=c.schcode and a.candno=c.candno and a.examyear=c.examyear and c.subjcode='A032'
    left outer join tb_hke_dsesubjresult d
    on a.suid=d.suid and a.schcode=d.schcode and a.candno=d.candno and a.examyear=d.examyear and d.subjcode='A070'
    left outer join tb_hke_dsesubjresult e
    on a.suid=e.suid and a.schcode=e.schcode and a.candno=e.candno and a.examyear=e.examyear and e.subjcode='A080'
    left outer join tb_hke_dsesubjresult f
    on a.suid=f.suid and a.schcode=f.schcode and a.candno=f.candno and a.examyear=f.examyear and f.subjcode='A090'
    left outer join tb_hke_dsesubjresult g
    on a.suid=g.suid and a.schcode=g.schcode and a.candno=g.candno and a.examyear=g.examyear and g.subjcode='A100'
    left outer join tb_hke_dsesubjresult h
    on a.suid=h.suid and a.schcode=h.schcode and a.candno=h.candno and a.examyear=h.examyear and h.subjcode='A110'
    left outer join tb_hke_dsesubjresult i
    on a.suid=i.suid and a.schcode=i.schcode and a.candno=i.candno and a.examyear=i.examyear and i.subjcode='A130'
    left outer join tb_hke_dsesubjresult j
    on a.suid=j.suid and a.schcode=j.schcode and a.candno=j.candno and a.examyear=j.examyear and j.subjcode='A140'
    left outer join tb_hke_dsesubjresult k
    on a.suid=k.suid and a.schcode=k.schcode and a.candno=k.candno and a.examyear=k.examyear and k.subjcode='A150'
    left outer join tb_hke_dsesubjresult l
    on a.suid=l.suid and a.schcode=l.schcode and a.candno=l.candno and a.examyear=l.examyear and l.subjcode='A165'
    left outer join tb_hke_dsesubjresult m
    on a.suid=m.suid and a.schcode=m.schcode and a.candno=m.candno and a.examyear=m.examyear and m.subjcode='A171'
    left outer join tb_hke_dsesubjresult n
    on a.suid=n.suid and a.schcode=n.schcode and a.candno=n.candno and a.examyear=n.examyear and n.subjcode='A180'
    left outer join tb_hke_dsesubjresult o
    on a.suid=o.suid and a.schcode=o.schcode and a.candno=o.candno and a.examyear=o.examyear and o.subjcode='A200'
    left outer join tb_hke_dsesubjresult p
    on a.suid=p.suid and a.schcode=p.schcode and a.candno=p.candno and a.examyear=p.examyear and p.subjcode='A230'
    left outer join tb_hke_dsesubjresult q
    on a.suid=q.suid and a.schcode=q.schcode and a.candno=q.candno and a.examyear=q.examyear and q.subjcode='A120'
    left outer join tb_hke_dsesubjresult r
    on a.suid=r.suid and a.schcode=r.schcode and a.candno=r.candno and a.examyear=r.examyear and r.subjcode='A190'
    left outer join tb_hke_dsesubjresult s
    on a.suid=s.suid and a.schcode=s.schcode and a.candno=s.candno and a.examyear=s.examyear and s.subjcode='A050'
    where a.examyear=?
    order by a.EXAMCODE,a.examyear,a.classcode,a.classno
     
    #1 humphrey, 2021-06-29
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    建議使用CASE WHEN 的功能,如果是"5*" 就出"6" , 而"5**" 就出"7"。

    在SQL 內,把原本的
    b0.subjgrdlvl 'A010 Chinese' ,
    改為
    (case when b0.subjgrdlvl='5**' then'7' when b0.subjgrdlvl='5*' then '6' else b0.subjgrdlvl end ) as 'A010 Chinese',
    就可以了,請自行更改餘下的科目(紫色的部分)。

    select
    a.EXAMCODE,
    a.examyear 'Exam Year',
    a.classcode 'Class',
    a.classno 'Class No.',
    a1.regno 'Registration No.',
    a1.ENNAME 'Name',
    a1.CHNAME 'Chinese Name',
    a1.sex 'Gender',
    (case when b0.subjgrdlvl='5**' then'7' when b0.subjgrdlvl='5*' then '6' else b0.subjgrdlvl end ) as 'A010 Chinese',
    (case when c0.subjgrdlvl='5**' then'7' when c0.subjgrdlvl='5*' then '6' else c0.subjgrdlvl end ) as 'A020 English',

    d0.subjgrdlvl 'A030 Math Compulsory',
    e0.subjgrdlvl 'A040 Liberal Studies',
    s.subjgrdlvl 'A050 CLI',
    d.subjgrdlvl 'A070 Chinese History',
    e.subjgrdlvl 'A080 Economics',
    f.subjgrdlvl 'A090 Ethics and Religious Studies',
    g.subjgrdlvl 'A100 Geography',
    h.subjgrdlvl 'A110 History',
    i.subjgrdlvl 'A130 Biology',
    j.subjgrdlvl 'A140 Chemistry',
    k.subjgrdlvl 'A150 Physics',
    m.subjgrdlvl 'A171 BASF',
    n.subjgrdlvl 'A180 Design and Applied Technology',
    o.subjgrdlvl 'A200 ICT',
    p.subjgrdlvl 'A230 Visual Arts',
    q.subjgrdlvl 'A120 TH',
    r.subjgrdlvl 'A190 HMSC',
    c.subjgrdlvl 'A032 M2'

    from wsadmin.tb_hke_dsecandinfo a
    left outer join wsadmin.tb_stu_student a1
    on a.suid=a1.suid and a.stuid=a1.stuid
    left outer join wsadmin.tb_hke_dsesubjresult b0
    on a.suid=b0.suid and a.schcode=b0.schcode and a.candno=b0.candno and a.examyear=b0.examyear and b0.subjcode='A010'
    left outer join wsadmin.tb_hke_dsesubjcompresult b1
    on b0.suid=b1.suid and b0.schcode=b1.schcode and b0.candno=b1.candno and b0.examyear=b1.examyear and b1.subjcode=b0.subjcode and b1.subjcompcode='1'
    left outer join wsadmin.tb_hke_dsesubjcompresult b2
    on b0.suid=b2.suid and b0.schcode=b2.schcode and b0.candno=b2.candno and b0.examyear=b2.examyear and b2.subjcode=b0.subjcode and b2.subjcompcode='2'
    left outer join wsadmin.tb_hke_dsesubjcompresult b3
    on b0.suid=b3.suid and b0.schcode=b3.schcode and b0.candno=b3.candno and b0.examyear=b3.examyear and b3.subjcode=b0.subjcode and b3.subjcompcode='3'
    left outer join wsadmin.tb_hke_dsesubjcompresult b4
    on b0.suid=b4.suid and b0.schcode=b4.schcode and b0.candno=b4.candno and b0.examyear=b4.examyear and b4.subjcode=b0.subjcode and b4.subjcompcode='4'
    left outer join wsadmin.tb_hke_dsesubjresult c0
    on a.suid=c0.suid and a.schcode=c0.schcode and a.candno=c0.candno and a.examyear=c0.examyear and c0.subjcode='A020'
    left outer join wsadmin.tb_hke_dsesubjcompresult c1
    on c0.suid=c1.suid and c0.schcode=c1.schcode and c0.candno=c1.candno and c0.examyear=c1.examyear and c1.subjcode=c0.subjcode and c1.subjcompcode='1'
    left outer join wsadmin.tb_hke_dsesubjcompresult c2
    on c0.suid=c2.suid and c0.schcode=c2.schcode and c0.candno=c2.candno and c0.examyear=c2.examyear and c2.subjcode=c0.subjcode and c2.subjcompcode='2'
    left outer join wsadmin.tb_hke_dsesubjcompresult c3
    on c0.suid=c3.suid and c0.schcode=c3.schcode and c0.candno=c3.candno and c0.examyear=c3.examyear and c3.subjcode=c0.subjcode and c3.subjcompcode='3'
    left outer join wsadmin.tb_hke_dsesubjcompresult c4
    on c0.suid=c4.suid and c0.schcode=c4.schcode and c0.candno=c4.candno and c0.examyear=c4.examyear and c4.subjcode=c0.subjcode and c4.subjcompcode='4'
    left outer join wsadmin.tb_hke_dsesubjresult d0
    on a.suid=d0.suid and a.schcode=d0.schcode and a.candno=d0.candno and a.examyear=d0.examyear and d0.subjcode='A030'
    left outer join wsadmin.tb_hke_dsesubjresult e0
    on a.suid=e0.suid and a.schcode=e0.schcode and a.candno=e0.candno and a.examyear=e0.examyear and e0.subjcode='A040'
    left outer join wsadmin.tb_hke_dsesubjresult b
    on a.suid=b.suid and a.schcode=b.schcode and a.candno=b.candno and a.examyear=b.examyear and b.subjcode='A031'
    left outer join wsadmin.tb_hke_dsesubjresult c
    on a.suid=c.suid and a.schcode=c.schcode and a.candno=c.candno and a.examyear=c.examyear and c.subjcode='A032'
    left outer join wsadmin.tb_hke_dsesubjresult d
    on a.suid=d.suid and a.schcode=d.schcode and a.candno=d.candno and a.examyear=d.examyear and d.subjcode='A070'
    left outer join wsadmin.tb_hke_dsesubjresult e
    on a.suid=e.suid and a.schcode=e.schcode and a.candno=e.candno and a.examyear=e.examyear and e.subjcode='A080'
    left outer join wsadmin.tb_hke_dsesubjresult f
    on a.suid=f.suid and a.schcode=f.schcode and a.candno=f.candno and a.examyear=f.examyear and f.subjcode='A090'
    left outer join wsadmin.tb_hke_dsesubjresult g
    on a.suid=g.suid and a.schcode=g.schcode and a.candno=g.candno and a.examyear=g.examyear and g.subjcode='A100'
    left outer join wsadmin.tb_hke_dsesubjresult h
    on a.suid=h.suid and a.schcode=h.schcode and a.candno=h.candno and a.examyear=h.examyear and h.subjcode='A110'
    left outer join wsadmin.tb_hke_dsesubjresult i
    on a.suid=i.suid and a.schcode=i.schcode and a.candno=i.candno and a.examyear=i.examyear and i.subjcode='A130'
    left outer join wsadmin.tb_hke_dsesubjresult j
    on a.suid=j.suid and a.schcode=j.schcode and a.candno=j.candno and a.examyear=j.examyear and j.subjcode='A140'
    left outer join wsadmin.tb_hke_dsesubjresult k
    on a.suid=k.suid and a.schcode=k.schcode and a.candno=k.candno and a.examyear=k.examyear and k.subjcode='A150'
    left outer join wsadmin.tb_hke_dsesubjresult l
    on a.suid=l.suid and a.schcode=l.schcode and a.candno=l.candno and a.examyear=l.examyear and l.subjcode='A165'
    left outer join wsadmin.tb_hke_dsesubjresult m
    on a.suid=m.suid and a.schcode=m.schcode and a.candno=m.candno and a.examyear=m.examyear and m.subjcode='A171'
    left outer join wsadmin.tb_hke_dsesubjresult n
    on a.suid=n.suid and a.schcode=n.schcode and a.candno=n.candno and a.examyear=n.examyear and n.subjcode='A180'
    left outer join wsadmin.tb_hke_dsesubjresult o
    on a.suid=o.suid and a.schcode=o.schcode and a.candno=o.candno and a.examyear=o.examyear and o.subjcode='A200'
    left outer join wsadmin.tb_hke_dsesubjresult p
    on a.suid=p.suid and a.schcode=p.schcode and a.candno=p.candno and a.examyear=p.examyear and p.subjcode='A230'
    left outer join wsadmin.tb_hke_dsesubjresult q
    on a.suid=q.suid and a.schcode=q.schcode and a.candno=q.candno and a.examyear=q.examyear and q.subjcode='A120'
    left outer join wsadmin.tb_hke_dsesubjresult r
    on a.suid=r.suid and a.schcode=r.schcode and a.candno=r.candno and a.examyear=r.examyear and r.subjcode='A190'
    left outer join wsadmin.tb_hke_dsesubjresult s
    on a.suid=s.suid and a.schcode=s.schcode and a.candno=s.candno and a.examyear=s.examyear and s.subjcode='A050'
    where a.examyear>= ?
    order by a.EXAMCODE,a.examyear,a.classcode,a.classno
     
    #2 edb-catherinewschan, 2021-06-29
  3. 10000322

    humphrey
    Expand Collapse

    文章:
    28
    讚:
    0
    THX, 方法成功了
    但當我同時將U及X轉為0的時候,出現了SQL more than 8000 character而無法儲存..

    SQL 如下
    (case when b0.subjgrdlvl='5**' then'7' when b0.subjgrdlvl='5*' then '6' when b0.subjgrdlvl='X' then'0' when b0.subjgrdlvl='U' then'0' else b0.subjgrdlvl end ) as 'A010 Chinese',

    可否化簡上述SQL?
     
    #3 humphrey, 2021-07-02
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,在WEBSAMS內執行SQL是有字數限制,不能超過8000個字。

    另外,SQL是有獨特格式,而你的要求是要因應輸出的資料,而顯示不同的"字眼",就要用這個方法做了,不能簡化。
     
    #4 edb-catherinewschan, 2021-07-06
  5. 55036990

    tr-cnchan1
    Expand Collapse

    文章:
    48
    讚:
    0
    修改後出現ERROR:
    SQL 錯誤: Exception:SQL Anywhere Error -143: Column 'subjgrdlvl' not found
    為什麼呢?
     
    #5 tr-cnchan1, 2021-07-15
  6. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,
    根據ERROR MESSAGE, 是說TABLE沒有"subjgrdlvl"這個FIELD,
    因為我不知你把SQL改到怎樣 ... 所以暫時回答不到你這個問題...
    請你把有問題的SQL貼上來,我可以查看一下.. .
     
    #6 edb-catherinewschan, 2021-07-15
    Last edited: 2021-07-16
  7. 55036990

    tr-cnchan1
    Expand Collapse

    文章:
    48
    讚:
    0
    select
    a.EXAMCODE,
    a.examyear 'Exam Year',
    a.classcode 'Class',
    a.classno 'Class No.',
    a1.regno 'Registration No.',
    a1.ENNAME 'Name',
    a1.CHNAME 'Chinese Name',
    a1.sex 'Gender',
    (case when b0.subjgrdlvl='5**' then '7' when b0.subjgrdlvl='5*' then '6' else b0.subjgrdlvl end) as 'A010 Chinese',
    (case when b1.subjcompgrdlvl='5**' then '7' when b1.subjcompgrdlvl='5*' then '6' else b1.subjcompgrdlvl end) as 'A010 Reading',
    (case when b2.subjcompgrdlvl='5**' then '7' when b2.subjcompgrdlvl='5*' then '6' else b2.subjcompgrdlvl end) as 'A010 Writing',
    (case when b3.subjcompgrdlvl='5**' then '7' when b3.subjcompgrdlvl='5*' then '6' else b3.subjcompgrdlvl end) as 'A010 Listening & Int Skills',
    (case when b4.subjcompgrdlvl='5**' then '7' when b4.subjcompgrdlvl='5*' then '6' else b4.subjcompgrdlvl end) as 'A010 Speaking',
    (case when c0.subjgrdlvl='5**' then '7' when c0.subjgrdlvl='5*' then '6' else c0.subjgrdlvl end) as 'A020 English',
    (case when c1.subjgrdlvl='5**' then '7' when c1.subjgrdlvl='5*' then '6' else c1.subjgrdlvl end) as 'A020 Reading',
    (case when c2.subjgrdlvl='5**' then '7' when c2.subjgrdlvl='5*' then '6' else c2.subjgrdlvl end) as 'A020 Writing',
    (case when c3.subjgrdlvl='5**' then '7' when c3.subjgrdlvl='5*' then '6' else c3.subjgrdlvl end) as 'A020 Listening & Int Skills',
    (case when c4.subjgrdlvl='5**' then '7' when c4.subjgrdlvl='5*' then '6' else c4.subjgrdlvl end) as 'A020 Speaking',
    (case when d0.subjgrdlvl='5**' then '7' when d0.subjgrdlvl='5*' then '6' else d0.subjgrdlvl end) as 'A030 Math Core',
    (case when e0.subjgrdlvl='5**' then '7' when e0.subjgrdlvl='5*' then '6' else e0.subjgrdlvl end) as 'A040 LS',
    (case when d.subjgrdlvl='5**' then '7' when d.subjgrdlvl='5*' then '6' else d.subjgrdlvl end) as 'A070 CHis',
    (case when e.subjgrdlvl='5**' then '7' when e.subjgrdlvl='5*' then '6' else e.subjgrdlvl end) as 'A080 Economics',
    (case when f.subjgrdlvl='5**' then '7' when f.subjgrdlvl='5*' then '6' else f.subjgrdlvl end) as 'A090 ERS',
    (case when g.subjgrdlvl='5**' then '7' when g.subjgrdlvl='5*' then '6' else g.subjgrdlvl end) as 'A100 Geography',
    (case when h.subjgrdlvl='5**' then '7' when h.subjgrdlvl='5*' then '6' else h.subjgrdlvl end) as 'A120 THS',
    (case when i.subjgrdlvl='5**' then '7' when i.subjgrdlvl='5*' then '6' else i.subjgrdlvl end) as 'A130 Biology',
    (case when j.subjgrdlvl='5**' then '7' when j.subjgrdlvl='5*' then '6' else j.subjgrdlvl end) as 'A140 Chemistry',
    (case when k.subjgrdlvl='5**' then '7' when k.subjgrdlvl='5*' then '6' else k.subjgrdlvl end) as 'A150 Physics',
    (case when m.subjgrdlvl='5**' then '7' when m.subjgrdlvl='5*' then '6' else m.subjgrdlvl end) as 'A171 BAFS-ACCT',
    (case when n.subjgrdlvl='5**' then '7' when n.subjgrdlvl='5*' then '6' else n.subjgrdlvl end) as 'A172 BAFS-BUSS',
    (case when o.subjgrdlvl='5**' then '7' when o.subjgrdlvl='5*' then '6' else o.subjgrdlvl end) as 'A200 ICT',
    (case when p.subjgrdlvl='5**' then '7' when p.subjgrdlvl='5*' then '6' else p.subjgrdlvl end) as 'A230 VA',
    (case when q.subjgrdlvl='5**' then '7' when q.subjgrdlvl='5*' then '6' else q.subjgrdlvl end) as 'A240 PE',
    (case when c.subjgrdlvl='5**' then '7' when c.subjgrdlvl='5*' then '6' else c.subjgrdlvl end) as 'A032 M2'
    from tb_hke_dsecandinfo a
    left outer join tb_stu_student a1
    on a.suid=a1.suid and a.stuid=a1.stuid
    left outer join tb_hke_dsesubjresult b0
    on a.suid=b0.suid and a.schcode=b0.schcode and a.candno=b0.candno and a.examyear=b0.examyear and b0.subjcode='A010'
    left outer join tb_hke_dsesubjcompresult b1
    on b0.suid=b1.suid and b0.schcode=b1.schcode and b0.candno=b1.candno and b0.examyear=b1.examyear and b1.subjcode=b0.subjcode and b1.subjcompcode='1'
    left outer join tb_hke_dsesubjcompresult b2
    on b0.suid=b2.suid and b0.schcode=b2.schcode and b0.candno=b2.candno and b0.examyear=b2.examyear and b2.subjcode=b0.subjcode and b2.subjcompcode='2'
    left outer join tb_hke_dsesubjcompresult b3
    on b0.suid=b3.suid and b0.schcode=b3.schcode and b0.candno=b3.candno and b0.examyear=b3.examyear and b3.subjcode=b0.subjcode and b3.subjcompcode='3'
    left outer join tb_hke_dsesubjcompresult b4
    on b0.suid=b4.suid and b0.schcode=b4.schcode and b0.candno=b4.candno and b0.examyear=b4.examyear and b4.subjcode=b0.subjcode and b4.subjcompcode='4'
    left outer join tb_hke_dsesubjresult c0
    on a.suid=c0.suid and a.schcode=c0.schcode and a.candno=c0.candno and a.examyear=c0.examyear and c0.subjcode='A020'
    left outer join tb_hke_dsesubjcompresult c1
    on c0.suid=c1.suid and c0.schcode=c1.schcode and c0.candno=c1.candno and c0.examyear=c1.examyear and c1.subjcode=c0.subjcode and c1.subjcompcode='1'
    left outer join tb_hke_dsesubjcompresult c2
    on c0.suid=c2.suid and c0.schcode=c2.schcode and c0.candno=c2.candno and c0.examyear=c2.examyear and c2.subjcode=c0.subjcode and c2.subjcompcode='2'
    left outer join tb_hke_dsesubjcompresult c3
    on c0.suid=c3.suid and c0.schcode=c3.schcode and c0.candno=c3.candno and c0.examyear=c3.examyear and c3.subjcode=c0.subjcode and c3.subjcompcode='3'
    left outer join tb_hke_dsesubjcompresult c4
    on c0.suid=c4.suid and c0.schcode=c4.schcode and c0.candno=c4.candno and c0.examyear=c4.examyear and c4.subjcode=c0.subjcode and c4.subjcompcode='4'
    left outer join tb_hke_dsesubjresult d0
    on a.suid=d0.suid and a.schcode=d0.schcode and a.candno=d0.candno and a.examyear=d0.examyear and d0.subjcode='A030'
    left outer join tb_hke_dsesubjresult e0
    on a.suid=e0.suid and a.schcode=e0.schcode and a.candno=e0.candno and a.examyear=e0.examyear and e0.subjcode='A040'
    left outer join tb_hke_dsesubjresult d
    on a.suid=d.suid and a.schcode=d.schcode and a.candno=d.candno and a.examyear=d.examyear and d.subjcode='A070'
    left outer join tb_hke_dsesubjresult e
    on a.suid=e.suid and a.schcode=e.schcode and a.candno=e.candno and a.examyear=e.examyear and e.subjcode='A080'
    left outer join tb_hke_dsesubjresult f
    on a.suid=f.suid and a.schcode=f.schcode and a.candno=f.candno and a.examyear=f.examyear and f.subjcode='A090'
    left outer join tb_hke_dsesubjresult g
    on a.suid=g.suid and a.schcode=g.schcode and a.candno=g.candno and a.examyear=g.examyear and g.subjcode='A100'
    left outer join tb_hke_dsesubjresult h
    on a.suid=h.suid and a.schcode=h.schcode and a.candno=h.candno and a.examyear=h.examyear and h.subjcode='A120'
    left outer join tb_hke_dsesubjresult i
    on a.suid=i.suid and a.schcode=i.schcode and a.candno=i.candno and a.examyear=i.examyear and i.subjcode='A130'
    left outer join tb_hke_dsesubjresult j
    on a.suid=j.suid and a.schcode=j.schcode and a.candno=j.candno and a.examyear=j.examyear and j.subjcode='A140'
    left outer join tb_hke_dsesubjresult k
    on a.suid=k.suid and a.schcode=k.schcode and a.candno=k.candno and a.examyear=k.examyear and k.subjcode='A150'
    left outer join tb_hke_dsesubjresult m
    on a.suid=m.suid and a.schcode=m.schcode and a.candno=m.candno and a.examyear=m.examyear and m.subjcode='A171'
    left outer join tb_hke_dsesubjresult n
    on a.suid=n.suid and a.schcode=n.schcode and a.candno=n.candno and a.examyear=n.examyear and n.subjcode='A172'
    left outer join tb_hke_dsesubjresult o
    on a.suid=o.suid and a.schcode=o.schcode and a.candno=o.candno and a.examyear=o.examyear and o.subjcode='A200'
    left outer join tb_hke_dsesubjresult p
    on a.suid=p.suid and a.schcode=p.schcode and a.candno=p.candno and a.examyear=p.examyear and p.subjcode='A230'
    left outer join tb_hke_dsesubjresult q
    on a.suid=q.suid and a.schcode=q.schcode and a.candno=q.candno and a.examyear=q.examyear and q.subjcode='A240'
    left outer join tb_hke_dsesubjresult c
    on a.suid=c.suid and a.schcode=c.schcode and a.candno=c.candno and a.examyear=c.examyear and c.subjcode='A032'
    where a.examyear=?
    order by a.EXAMCODE,a.examyear,a.classcode,a.classno
     
    #7 tr-cnchan1, 2021-07-16
  8. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    207
    讚:
    0
    你好,

    主科用 subjgrdlvl , 分卷用 subjcompgrdlvl , 所以c1~c4是用
    subjcompgrdlvl 而不是subjgrdlvl

    你看看下面,c1和c2 是分卷
    p1.JPG

    所以要用 subjcompgrdlvl
    p2.JPG
     
    #8 edb-catherinewschan, 2021-07-19