SQL 加上奬懲資料

本文由 chautc 在 2020-07-23 發表於 "WebSAMS 討論區" 討論區

  1. 10004315

    chautc
    Expand Collapse

    文章:
    58
    讚:
    0
    下列SQL 可以加上 大功,小功,優點,大過,小過,缺點 的奬懲資料嗎?
    謝謝!

    資料結構如下:

    班別
    學號
    性別
    姓名
    全年總平均分
    全年班名次
    全年級名次
    T1操行
    T2操行
    全年缺席
    全年遲到
    全年早退
    大功
    小功
    優點
    大過
    小過
    缺點




    select
    a.regno 'Regno',
    a.classcode '班別',
    a.classno '學號',
    a.sex '性別',
    a.chname '姓名',
    d.syspercscore '全年總平均分',
    d.omclass||'/'||d.omclassbase '全年班名次',
    d.omclasslvl||'/'||d.omclasslvlbase '全年級名次',
    d1.overcondgradeconversioncompcode '上學期操行',
    d2.overcondgradeconversioncompcode '下學期操行',
    g.total - g.waive '全年缺席次數',
    f.total - f.waive '全年遲到次數',
    h.total '全年早退次數'
    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and d.schyear=a.schyear and d.timeseq=1000
    left outer join tb_asr_studassessdata d1
    on a.suid=d1.suid and a.stuid=d1.stuid and d1.schyear=a.schyear and d1.timeseq=1100
    left outer join tb_asr_studassessdata d2
    on a.suid=d2.suid and a.stuid=d2.stuid and d2.schyear=a.schyear and d2.timeseq=1200
    left outer join
    (select c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT, sum

    (c.NONATTCOUNT)'TOTAL', sum(case when hl.flag_1 = 1 then c.NONATTCOUNT else 0 end) 'WAIVE'
    from TB_ATT_NONATT c
    join TB_HSE_COMMON hl
    on hl.suid=c.suid and hl.code_id=c.nonattcode and hl.tb_id='SCHLAT'
    where c.ANPRECID is not null
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) f
    on a.SUID = f.SUID and a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.NONATTTYPE = 'LATE'
    left outer join
    (select c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT, sum(c.NONATTCOUNT)

    'TOTAL', sum(case when ha.flag_3 = 1 then c.NONATTCOUNT else 0 end) 'WAIVE'
    from TB_ATT_NONATT c
    join TB_HSE_COMMON ha
    on ha.suid=c.suid and ha.code_id=c.nonattcode and ha.tb_id='SCHABS'
    where c.ANPRECID is not null
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) g
    on a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.NONATTTYPE = 'ABSNT'
    left outer join
    (select c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT, sum(c.NONATTCOUNT)

    'TOTAL', sum(case when he.flag_1 = 1 then c.NONATTCOUNT else 0 end) 'WAIVE'
    from TB_ATT_NONATT c
    join TB_HSE_COMMON he
    on he.suid=c.suid and he.code_id=c.nonattcode and he.tb_id='SCHEARL'
    where c.ANPRECID is not null
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) h
    on a.SUID = h.SUID and a.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.NONATTTYPE = 'LEAVE'
    where a.schyear=?
    order by a.classlvl, a.classcode, a.classno
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    你好

    請參考以下的SQL, 由於系統分優點, 缺點各5個LEVEL, 請自行查看那個是優點大功小功

    另外, k.TIMESEQ 是決定抽取那時段的優點, 缺點數值

    select
    a.schyear,
    a.regno 'Regno',
    a.classcode '班別',
    a.classno '學號',
    a.sex '性別',
    a.chname '姓名',
    d.syspercscore '全年總平均分',
    d.omclass||'/'||d.omclassbase '全年班名次',
    d.omclasslvl||'/'||d.omclasslvlbase '全年級名次',
    d1.overcondgradeconversioncompcode '上學期操行',
    d2.overcondgradeconversioncompcode '下學期操行',
    g.total - g.waive '全年缺席次數',
    f.total - f.waive '全年遲到次數',
    h.total '全年早退次數',

    convert(numeric(10,2),(case when k.ADJLVL5MERIT > 0 or k.LVL5MERIT > 0
    then (case when k.ADJLVL5MERIT > 0 then k.ADJLVL5MERIT else k.LVL5MERIT end) else ''
    end)) 'MERIT_LVL5',
    convert(numeric(10,2),(case when k.ADJLVL4MERIT > 0 or k.LVL4MERIT > 0
    then (case when k.ADJLVL4MERIT > 0 then k.ADJLVL4MERIT else k.LVL4MERIT end) else ''
    end)) 'MERIT_LVL4',
    convert(numeric(10,2),(case when k.ADJLVL3MERIT > 0 or k.LVL3MERIT > 0
    then (case when k.ADJLVL3MERIT > 0 then k.ADJLVL3MERIT else k.LVL3MERIT end) else ''
    end)) 'MERIT_LVL3',
    convert(numeric(10,2),(case when k.ADJLVL2MERIT > 0 or k.LVL2MERIT > 0
    then (case when k.ADJLVL2MERIT > 0 then k.ADJLVL2MERIT else k.LVL2MERIT end) else ''
    end)) 'MERIT_LVL2',
    convert(numeric(10,2),(case when k.ADJLVL1MERIT > 0 or k.LVL1MERIT > 0
    then (case when k.ADJLVL1MERIT > 0 then k.ADJLVL1MERIT else k.LVL1MERIT end) else ''
    end)) 'MERIT_LVL1',
    convert(numeric(10,2),(case when k.ADJLVL5DEMERIT > 0 or k.LVL5DEMERIT > 0
    then (case when k.ADJLVL5DEMERIT > 0 then k.ADJLVL5DEMERIT else k.LVL5DEMERIT end) else ''
    end)) 'DEMERIT_LVL5',
    convert(numeric(10,2),(case when k.ADJLVL4DEMERIT > 0 or k.LVL4DEMERIT > 0
    then (case when k.ADJLVL4DEMERIT > 0 then k.ADJLVL4DEMERIT else k.LVL4DEMERIT end) else ''
    end)) 'DEMERIT_LVL4',
    convert(numeric(10,2),(case when k.ADJLVL3DEMERIT > 0 or k.LVL3DEMERIT > 0
    then (case when k.ADJLVL3DEMERIT > 0 then k.ADJLVL3DEMERIT else k.LVL3DEMERIT end) else ''
    end)) 'DEMERIT_LVL3',
    convert(numeric(10,2),(case when k.ADJLVL2DEMERIT > 0 or k.LVL2DEMERIT > 0
    then (case when k.ADJLVL2DEMERIT > 0 then k.ADJLVL2DEMERIT else k.LVL2DEMERIT end) else ''
    end)) 'DEMERIT_LVL2',
    convert(numeric(10,2),(case when k.ADJLVL1DEMERIT > 0 or k.LVL1DEMERIT > 0
    then (case when k.ADJLVL1DEMERIT > 0 then k.ADJLVL1DEMERIT else k.LVL1DEMERIT end) else ''
    end)) 'DEMERIT_LVL1'


    from vw_stu_lateststudent a
    left outer join tb_asr_studassessdata d
    on a.suid=d.suid and a.stuid=d.stuid and d.schyear=a.schyear and d.timeseq=1000
    left outer join tb_asr_studassessdata d1
    on a.suid=d1.suid and a.stuid=d1.stuid and d1.schyear=a.schyear and d1.timeseq=1100
    left outer join tb_asr_studassessdata d2
    on a.suid=d2.suid and a.stuid=d2.stuid and d2.schyear=a.schyear and d2.timeseq=1200
    left outer join
    (select c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT, sum

    (c.NONATTCOUNT)'TOTAL', sum(case when hl.flag_1 = 1 then c.NONATTCOUNT else 0 end) 'WAIVE'
    from TB_ATT_NONATT c
    join TB_HSE_COMMON hl
    on hl.suid=c.suid and hl.code_id=c.nonattcode and hl.tb_id='SCHLAT'
    where c.ANPRECID is not null
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) f
    on a.SUID = f.SUID and a.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and f.NONATTTYPE = 'LATE'
    left outer join
    (select c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT, sum(c.NONATTCOUNT)

    'TOTAL', sum(case when ha.flag_3 = 1 then c.NONATTCOUNT else 0 end) 'WAIVE'
    from TB_ATT_NONATT c
    join TB_HSE_COMMON ha
    on ha.suid=c.suid and ha.code_id=c.nonattcode and ha.tb_id='SCHABS'
    where c.ANPRECID is not null
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) g
    on a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and g.NONATTTYPE = 'ABSNT'
    left outer join
    (select c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT, sum(c.NONATTCOUNT)

    'TOTAL', sum(case when he.flag_1 = 1 then c.NONATTCOUNT else 0 end) 'WAIVE'
    from TB_ATT_NONATT c
    join TB_HSE_COMMON he
    on he.suid=c.suid and he.code_id=c.nonattcode and he.tb_id='SCHEARL'
    where c.ANPRECID is not null
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) h
    on a.SUID = h.SUID and a.SCHYEAR = h.SCHYEAR and a.STUID = h.STUID and h.NONATTTYPE = 'LEAVE'

    left outer join wsadmin.TB_ASR_STUDMISCDATA k
    on k.SUID=a.suid and k.STUID=a.stuid and k.SCHYEAR=a.schyear and k.TIMESEQ=?


    where a.schyear=?
    order by a.classlvl, a.classcode, a.classno
     
    #2 edb-catherinewschan, 2020-08-05 , 12:24 上午