# SQL 加上奬懲資料

1. ### 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 '全年級名次',
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. ### 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 '全年級名次',
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'