SQL SQL

本文由 cantsy 在 2019-11-30 發表於 "WebSAMS 討論區" 討論區

  1. 56560977

    cantsy
    Expand Collapse

    文章:
    120
    讚:
    0
    求SQL 顯示學生 1. 缺席日數, 2. 大過, 3. 小過, 4. 缺點次數, 以班別, 班號排列, 謝
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    83
    讚:
    0
    你好,

    可以參考 網上校管系統 — 常用SQL參考庫
    網址是: https://www.websams.edb.gov.hk//sql/html/SQL_Q&A.html#

    當中的 獎懲資料 (下載), 有一條近似的SQL
    1. 抽取各級學生的獎懲統計資料如下:

    加入 a.ANPTYPE='P' 的條件句 , 把懲罰的部份抽出來就可以... ..
    我也把SQL改了,請參考一下:

    select
    c.CLASSNAME,
    b.CLASSNO,
    b.CHNAME,
    b.enname,
    trim((case when trim(a.PREFIX) <> ''
    then trim(a.PREFIX)
    else ''
    end) +
    (case when trim(a.PREFIX) <> '' and
    (right(trim(a.PREFIX),1) between char(33) and char(126) or
    left(trim(a.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 g.TOTAL>0
    then (case when g.NONATTTYPE = 'ABSNT'
    then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天'
    else '遲到' + cast(g.TOTAL as text) + '次'
    end)
    when a.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 a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
    then trim(h.CH_DES)
    else trim(a.DESCRIPTIONSTR)
    end)
    end) +
    (case when trim(a.POSITION) <> '' and
    (right(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
    right(trim(h.CH_DES),1) between char(33) and char(126) or
    left(trim(a.POSITION),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when trim(a.POSITION) <> ''
    then trim(a.POSITION)
    else ''
    end) +
    (case when d.SCHEME = 'M'
    then (case when a.LVL1>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1CHIDESC
    else n.PUNISH1CHIDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL2>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2CHIDESC
    else n.PUNISH2CHIDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL3>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3CHIDESC
    else n.PUNISH3CHIDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL4>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4CHIDESC
    else n.PUNISH4CHIDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL5>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5CHIDESC
    else n.PUNISH5CHIDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
    else ''
    end)
    else ''
    end)) 'EVENT_CH'
    from wsadmin.TB_STU_ANPSTUREC a
    join wsadmin.VW_STU_LATESTSTUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and a.SCHYEAR =?
    join wsadmin.TB_SCH_SCHCLASS c
    on a.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 a.SUID = d.SUID and a.SCHYEAR = d.SCHYEAR
    left outer join wsadmin.TB_NAA_NAAOFFERED e
    on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.NAAOFFEREDID = e.NAAOFFEREDID
    left outer join wsadmin.TB_NAA_STUDENTNAA f
    on a.SUID = f.SUID and a.STUID = f.STUID and a.NAAOFFEREDID = f.NAAOFFEREDID
    left outer join
    (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
    from wsadmin.TB_ATT_NONATT
    where ANPRECID is not null
    group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) g
    on a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and a.ANPRECID = g.ANPRECID
    left outer join wsadmin.TB_HSE_COMMON h
    on a.SUID = h.SUID and a.DESCRIPTIONCODE = h.CODE_ID and
    ((a.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (a.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join wsadmin.TB_HSE_COMMON i on
    a.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
    a.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join wsadmin.TB_HSE_COMMON k on
    a.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join wsadmin.TB_HSE_COMMON l on
    a.SUID = l.SUID and a.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF'
    left outer join wsadmin.TB_HSE_COMMON m on
    a.SUID = m.SUID and a.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join wsadmin.TB_STU_ANPSCHEMEDETAILMD n on
    a.SUID = n.SUID and a.SCHYEAR = n.SCHYEAR
    left outer join wsadmin.VW_ASR_STAFF o on
    a.SUID = o.SUID and a.STAFFCODE = o.STAFFCODE
    where a.ANPTYPE='P'
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
     
    #2 edb-catherinewschan, 2019-12-03
    Last edited: 2019-12-04
  3. 56560977

    cantsy
    Expand Collapse

    文章:
    120
    讚:
    0
    無顯示缺席日數!!!
     
  4. 56560977

    cantsy
    Expand Collapse

    文章:
    120
    讚:
    0
    以上SQL無法顯示缺席日數!!! THX
     
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    83
    讚:
    0
    我是有的, 可能你不是在 "學生出席資料"模組內輸入缺席資料

    我加插了SQL語句, 請試試看

    select
    c.CLASSNAME,
    b.CLASSNO,
    b.CHNAME,
    b.enname,
    trim((case when trim(a.PREFIX) <> ''
    then trim(a.PREFIX)
    else ''
    end) +
    (case when trim(a.PREFIX) <> '' and
    (right(trim(a.PREFIX),1) between char(33) and char(126) or
    left(trim(a.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 g.TOTAL>0
    then (case when g.NONATTTYPE = 'ABSNT'
    then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天'
    else '遲到' + cast(g.TOTAL as text) + '次'
    end)
    when a.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 a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
    then trim(h.CH_DES)
    else trim(a.DESCRIPTIONSTR)
    end)
    end) +
    (case when trim(a.POSITION) <> '' and
    (right(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
    right(trim(h.CH_DES),1) between char(33) and char(126) or
    left(trim(a.POSITION),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when trim(a.POSITION) <> ''
    then trim(a.POSITION)
    else ''
    end) +
    (case when d.SCHEME = 'M'
    then (case when a.LVL1>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1CHIDESC
    else n.PUNISH1CHIDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL2>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2CHIDESC
    else n.PUNISH2CHIDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL3>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3CHIDESC
    else n.PUNISH3CHIDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL4>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4CHIDESC
    else n.PUNISH4CHIDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL5>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5CHIDESC
    else n.PUNISH5CHIDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
    else ''
    end)
    else ''
    end)) 'EVENT_CH',
    s.ADJABSENTDAY '缺席日數'
    from wsadmin.TB_STU_ANPSTUREC a
    join wsadmin.VW_STU_LATESTSTUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and a.SCHYEAR =?
    join wsadmin.TB_SCH_SCHCLASS c
    on a.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 a.SUID = d.SUID and a.SCHYEAR = d.SCHYEAR
    left outer join wsadmin.TB_NAA_NAAOFFERED e
    on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.NAAOFFEREDID = e.NAAOFFEREDID
    left outer join wsadmin.TB_NAA_STUDENTNAA f
    on a.SUID = f.SUID and a.STUID = f.STUID and a.NAAOFFEREDID = f.NAAOFFEREDID
    left outer join
    (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
    from wsadmin.TB_ATT_NONATT
    where ANPRECID is not null
    group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) g
    on a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and a.ANPRECID = g.ANPRECID
    left outer join wsadmin.TB_HSE_COMMON h
    on a.SUID = h.SUID and a.DESCRIPTIONCODE = h.CODE_ID and
    ((a.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (a.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join wsadmin.TB_HSE_COMMON i on
    a.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
    a.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join wsadmin.TB_HSE_COMMON k on
    a.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join wsadmin.TB_HSE_COMMON l on
    a.SUID = l.SUID and a.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF'
    left outer join wsadmin.TB_HSE_COMMON m on
    a.SUID = m.SUID and a.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join wsadmin.TB_STU_ANPSCHEMEDETAILMD n on
    a.SUID = n.SUID and a.SCHYEAR = n.SCHYEAR
    left outer join wsadmin.VW_ASR_STAFF o on
    a.SUID = o.SUID and a.STAFFCODE = o.STAFFCODE
    left outer join wsadmin.TB_STU_STUSCHREC r on
    r.SUID = b.SUID AND r.STUID = b.STUID and r.schyear = b.schyear
    left outer join wsadmin.TB_ASR_STUDMISCDATA s on
    r.SUID = s.SUID AND r.STUID = s.STUID AND r.SCHYEAR = s.SCHYEAR
    where a.ANPTYPE='P'
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
     
    #5 edb-catherinewschan, 2019-12-05
  6. 56560977

    cantsy
    Expand Collapse

    文章:
    120
    讚:
    0
    無論入2018 或2019 , 缺席日數為吉的
     
  7. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    83
    讚:
    0
    請執行以下的SQL語句, 看看有沒有顯示缺席的資料


    SELECT R.CLASSCODE, R.CLASSNO, STRN, REGNO, ENNAME, CHNAME, ADJABSENTDAY, ADJLATEDAY, ADJEARLYLEAVEDAY
    FROM TB_STU_STUSCHREC R
    JOIN TB_STU_STUDENT S
    ON R.SUID = S.SUID AND R.STUID = S.STUID JOIN TB_ASR_STUDMISCDATA A
    ON A.SUID = R.SUID AND A.STUID = R.STUID AND A.SCHYEAR = R.SCHYEAR
    WHERE R.SCHYEAR = ? AND R.CLASSLVL = ? AND TIMESEQ = ?


    如果仍然沒有, 請連絡所屬的學校聯絡主任作進一步的了解及跟進, 謝謝
     
    #7 edb-catherinewschan, 2019-12-06 , 10:04 上午
  8. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    83
    讚:
    0
    你好,

    發現 TB_ATT_NONATT 和 TB_STU_ANPSTUREC 的TABLE 內ANPRECID的內容不一致, 所以引致資料連繫不到, 我已刪除了這項連繫。

    以下就是已改的SQL, 並加上缺席日數 及 遲到次數



    select
    c.CLASSNAME,
    b.CLASSNO,
    b.CHNAME,
    b.enname,
    trim((case when trim(a.PREFIX) <> ''
    then trim(a.PREFIX)
    else ''
    end) +
    (case when trim(a.PREFIX) <> '' and
    (right(trim(a.PREFIX),1) between char(33) and char(126) or
    left(trim(a.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 a.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 a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
    then trim(h.CH_DES)
    else trim(a.DESCRIPTIONSTR)
    end)
    end) +
    (case when trim(a.POSITION) <> '' and
    (right(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
    right(trim(h.CH_DES),1) between char(33) and char(126) or
    left(trim(a.POSITION),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when trim(a.POSITION) <> ''
    then trim(a.POSITION)
    else ''
    end) +
    (case when d.SCHEME = 'M'
    then (case when a.LVL1>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1CHIDESC
    else n.PUNISH1CHIDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL2>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2CHIDESC
    else n.PUNISH2CHIDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL3>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3CHIDESC
    else n.PUNISH3CHIDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL4>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4CHIDESC
    else n.PUNISH4CHIDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL5>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5CHIDESC
    else n.PUNISH5CHIDESC end) + ' ' + cast(a.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 a.SUID = kk.SUID and a.SCHYEAR = kk.SCHYEAR and a.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 a.SUID = qq.SUID and a.SCHYEAR = qq.SCHYEAR and a.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 a
    join wsadmin.VW_STU_LATESTSTUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and a.SCHYEAR = ?
    join wsadmin.TB_SCH_SCHCLASS c
    on a.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 a.SUID = d.SUID and a.SCHYEAR = d.SCHYEAR
    left outer join wsadmin.TB_NAA_NAAOFFERED e
    on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.NAAOFFEREDID = e.NAAOFFEREDID
    left outer join wsadmin.TB_NAA_STUDENTNAA f
    on a.SUID = f.SUID and a.STUID = f.STUID and a.NAAOFFEREDID = f.NAAOFFEREDID
    left outer join wsadmin.TB_HSE_COMMON h
    on a.SUID = h.SUID and a.DESCRIPTIONCODE = h.CODE_ID and
    ((a.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (a.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join wsadmin.TB_HSE_COMMON i on
    a.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
    a.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join wsadmin.TB_HSE_COMMON k on
    a.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join wsadmin.TB_HSE_COMMON l on
    a.SUID = l.SUID and a.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF'
    left outer join wsadmin.TB_HSE_COMMON m on
    a.SUID = m.SUID and a.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join wsadmin.TB_STU_ANPSCHEMEDETAILMD n on
    a.SUID = n.SUID and a.SCHYEAR = n.SCHYEAR
    left outer join wsadmin.VW_ASR_STAFF o on
    a.SUID = o.SUID and a.STAFFCODE = o.STAFFCODE
    where a.ANPTYPE='P'
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
     
    #8 edb-catherinewschan, 2019-12-06 , 3:53 下午
  9. 56560977

    cantsy
    Expand Collapse

    文章:
    120
    讚:
    0
    可以顯示了, 謝謝