SQL SQL 顯示優點, 缺點, 缺席, 遲到, 早退

本文由 cantsy 在 2020-07-16 發表於 "WebSAMS 討論區" 討論區

  1. 56560977

    cantsy
    Expand Collapse

    文章:
    126
    讚:
    0
    以下 SQL 只能顯示 缺點, 缺席, 遲到, 謝謝

    select
    c.CLASSNAME,
    b.CLASSNO,
    b.CHNAME,
    b.enname,
    trim((case when trim(z.PREFIX) <> ''
    then trim(z.PREFIX)
    else ''
    end) +
    (case when trim(z.PREFIX) <> '' and
    (right(trim(z.PREFIX),1) between char(33) and char(126) or
    left(trim(z.DESCRIPTIONSTR),1) between char(33) and char(126) or
    left(trim(h.CH_DES),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when z.NAAOFFEREDID>0
    then (case when f.NAAPOSTCODE is null and f.NAAPERFCODE is null
    then i.CH_DES
    when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
    then i.CH_DES + j.CH_DES
    when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
    then i.CH_DES + ' (' + k.CH_DES + ')'
    else i.CH_DES + j.CH_DES + ' (' + k.CH_DES + ')'
    end)
    else (case when z.DESCRIPTIONCODE is not null and trim(z.DESCRIPTIONCODE) <> ''
    then trim(h.CH_DES)
    else trim(z.DESCRIPTIONSTR)
    end)
    end) +
    (case when trim(z.POSITION) <> '' and
    (right(trim(z.DESCRIPTIONSTR),1) between char(33) and char(126) or
    right(trim(h.CH_DES),1) between char(33) and char(126) or
    left(trim(z.POSITION),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when trim(z.POSITION) <> ''
    then trim(z.POSITION)
    else ''
    end) +
    (case when d.SCHEME = 'M'
    then (case when z.LVL1>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD1CHIDESC
    else n.PUNISH1CHIDESC end) + ' ' + cast(z.LVL1 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL2>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD2CHIDESC
    else n.PUNISH2CHIDESC end) + ' ' + cast(z.LVL2 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL3>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD3CHIDESC
    else n.PUNISH3CHIDESC end) + ' ' + cast(z.LVL3 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL4>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD4CHIDESC
    else n.PUNISH4CHIDESC end) + ' ' + cast(z.LVL4 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL5>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD5CHIDESC
    else n.PUNISH5CHIDESC end) + ' ' + cast(z.LVL5 as text) + ' 次'
    else ''
    end)
    else ''
    end)) 'EVENT_CH',
    (select sum(kk.NONATTCOUNT) as 'TOTAL'
    from wsadmin.TB_ATT_NONATT kk
    where kk.ANPRECID is not null and z.SUID = kk.SUID and z.SCHYEAR = kk.SCHYEAR and z.STUID = kk.STUID and kk.NONATTTYPE='ABSNT'
    group by kk.SUID, kk.SCHYEAR, kk.STUID, kk.ANPRECID, kk.NONATTTYPE, kk.NONATTCOUNT
    ) as '缺席日數',
    (select sum(qq.NONATTCOUNT) as 'TOTAL'
    from wsadmin.TB_ATT_NONATT qq
    where qq.ANPRECID is not null and z.SUID = qq.SUID and z.SCHYEAR = qq.SCHYEAR and z.STUID = qq.STUID and qq.NONATTTYPE='LATE'
    group by qq.SUID, qq.SCHYEAR, qq.STUID, qq.ANPRECID, qq.NONATTTYPE, qq.NONATTCOUNT
    ) as '遲到次數'
    from wsadmin.TB_STU_ANPSTUREC z
    join wsadmin.VW_STU_LATESTSTUDENT b
    on z.SUID = b.SUID and z.STUID = b.STUID and z.SCHYEAR = b.SCHYEAR and z.SCHYEAR = ?
    join wsadmin.TB_SCH_SCHCLASS c
    on z.SUID = c.SUID and b.SCHYEAR = c.SCHYEAR and b.SCHLVL = c.SCHLEVEL and
    b.SCHSESS = c.SCHSESSION and b.CLASSLVL = c.CLASSLEVEL and b.CLASSCODE = c.CLASSCODE
    join wsadmin.TB_STU_ANPSCHEME d
    on z.SUID = d.SUID and z.SCHYEAR = d.SCHYEAR
    left outer join wsadmin.TB_NAA_NAAOFFERED e
    on z.SUID = e.SUID and z.SCHYEAR = e.SCHYEAR and z.NAAOFFEREDID = e.NAAOFFEREDID
    left outer join wsadmin.TB_NAA_STUDENTNAA f
    on z.SUID = f.SUID and z.STUID = f.STUID and z.NAAOFFEREDID = f.NAAOFFEREDID
    left outer join wsadmin.TB_HSE_COMMON h
    on z.SUID = h.SUID and z.DESCRIPTIONCODE = h.CODE_ID and
    ((z.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (z.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join wsadmin.TB_HSE_COMMON i on
    z.SUID = i.SUID and e.NAACODE = i.CODE_ID and
    ((i.TB_ID = 'ECACD' and e.NAATYPE = 'E') or
    (i.TB_ID = 'INTERSCH' and e.NAATYPE = 'I') or
    (i.TB_ID = 'SEVPST' and e.NAATYPE = 'S'))
    left outer join wsadmin.TB_HSE_COMMON j on
    z.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join wsadmin.TB_HSE_COMMON k on
    z.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join wsadmin.TB_HSE_COMMON l on
    z.SUID = l.SUID and z.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF'
    left outer join wsadmin.TB_HSE_COMMON m on
    z.SUID = m.SUID and z.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join wsadmin.TB_STU_ANPSCHEMEDETAILMD n on
    z.SUID = n.SUID and z.SCHYEAR = n.SCHYEAR
    left outer join wsadmin.VW_ASR_STAFF o on
    z.SUID = o.SUID and z.STAFFCODE = o.STAFFCODE
    where z.ANPTYPE='P'
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE
     
  2. 56560977

    cantsy
    Expand Collapse

    文章:
    126
    讚:
    0
    只需列出數量: 例如
    班別, 班號, 優點, 缺點, 缺席, 遲到, 早退
    1K 12 3 2 2 1 0
     
  3. 56560977

    cantsy
    Expand Collapse

    文章:
    126
    讚:
    0
    接班別顥示每位學生
    班別, 班號, 優點, 缺點, 缺席, 遲到, 早退
    1K 01 1 0 0 1 0
    1K 02 0 2 3 2 0
    ......
     
  4. 56560977

    cantsy
    Expand Collapse

    文章:
    126
    讚:
    0
    為什麼無回應
     
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    由於SQL過於複雜,WEBSAMS系統不支援語法, 所以不能在WEBSAMS執行。
    如需進一步跟進,請聯絡校主。謝謝
     
    #5 edb-catherinewschan, 2020-07-29