如何用sql抽取獎懲紀錄?

websams 內置的report 21c可依班抽取學生獎懲資料,但本人想把資料改為依類別,以方便印發report給有關老師核對資料,應如何編寫SQL ?

例:

班別  學號  學生姓名     項目   大功  小功  優點  負責老師

6A        2    陳小明       風紀     0     0       1     王

6A      15    陳大明       風紀     0     0       1     王

班別  學號  學生姓名     項目   大功  小功  優點  負責老師

6A      2    陳小明      手球隊     0     0       1     吳

6B    15    王大明      手球隊     0     0       1     吳

 

 

 

 

無法使用SQL讓標題重複。

select
    a.SCHYEAR '學年',
    a.CLASSLVL '級別',
    c.CLASSNAME '班別',
    a.CLASSNO '班號',
    a.CHNAME '中文姓名',
    dateformat(b.ANPDATE, 'DD/MM/YYYY') '獎勵日期',
    trim((case when trim(b.PREFIX) <> ''
         then trim(b.PREFIX)
         else ''
         end) +
        (case when trim(b.PREFIX) <> '' and
             (right(trim(b.PREFIX),1) between char(33) and char(126) or
             left(trim(b.DESCRIPTIONSTR),1) between char(33) and char(126) or
             left(trim(g.CH_DES),1) between char(33) and char(126))
         then ' '
         else ''
         end) +     
        (case when f.TOTAL > 0
         then (case when f.NONATTTYPE = 'ABSNT'
               then '缺席' + cast(cast(f.TOTAL as numeric(10,1)) as text) + '天'
               else '遲到' + cast(f.TOTAL as text) + '次'
               end)
         else (case when b.NAAOFFEREDID > 0
               then (case when e.NAAPOSTCODE is null and e.NAAPERFCODE is null
                     then h.CH_DES
                     else (case when e.NAAPOSTCODE is null or e.NAAPERFCODE is null
                           then (case when e.NAAPERFCODE is null
                                 then h.CH_DES + i.CH_DES
                                 else h.CH_DES + ' (' + j.CH_DES + ')'    
                                 end)
                           else h.CH_DES + i.CH_DES + ' (' + j.CH_DES + ')'
                           end)
                     end)
               else (case when trim(b.DESCRIPTIONSTR) <> ''
                     then b.DESCRIPTIONSTR
                     else g.CH_DES
                     end)
               end)
         end) +
        (case when trim(b.POSITION) <> '' and
             (right(trim(b.DESCRIPTIONSTR),1) between char(33) and char(126) or
             right(trim(g.CH_DES),1) between char(33) and char(126) or
             left(trim(b.POSITION),1) between char(33) and char(126))
         then ' '
         else ''
         end) +  
        (case when trim(b.POSITION) <> ''
         then trim(b.POSITION)
         else ''
         end)) '項目',
    b.LVL3 '大功',
    b.LVL2 '小功',
    b.LVL1 '優點',
    k.NAME_CHI '負責老師'
from VW_STU_LATESTSTUDENT a
join TB_STU_ANPSTUREC b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and
       b.ANPTYPE = 'A' and a.SCHYEAR = ?
join TB_SCH_SCHCLASS c
    on a.SUID = c.SUID and a.SCHYEAR = c.SCHYEAR and a.SCHLVL = c.SCHLEVEL and
       a.SCHSESS = c.SCHSESSION and a.CLASSLVL = c.CLASSLEVEL and
       a.CLASSCODE = c.CLASSCODE
left outer join TB_NAA_NAAOFFERED d
    on a.SUID = d.SUID and b.SCHYEAR = d.SCHYEAR and b.NAAOFFEREDID = d.NAAOFFEREDID
left outer join TB_NAA_STUDENTNAA e
    on a.SUID = e.SUID and a.STUID = e.STUID and d.NAAOFFEREDID = e.NAAOFFEREDID
left outer join
    (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE,
         NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
     from TB_ATT_NONATT
     where ANPRECID is not null
     group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) f
    on a.SUID = f.SUID and b.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and
       b.ANPRECID = f.ANPRECID
left outer join TB_HSE_COMMON g
    on a.SUID = g.SUID and b.DESCRIPTIONCODE = g.CODE_ID and g.TB_ID = 'PRIZE'
left outer join wsadmin.TB_HSE_COMMON h on
    a.SUID = h.SUID and d.NAACODE = h.CODE_ID and
    ((h.TB_ID = 'ECACD' and d.NAATYPE = 'E') or
    (h.TB_ID = 'INTERSCH' and d.NAATYPE = 'I') or
    (h.TB_ID = 'SEVPST' and d.NAATYPE = 'S'))
left outer join TB_HSE_COMMON i on
    a.SUID = i.SUID and e.NAAPOSTCODE = i.CODE_ID and i.TB_ID = 'ECAPST'
left outer join TB_HSE_COMMON j on
    a.SUID = j.SUID and e.NAAPERFCODE = j.CODE_ID and j.TB_ID = 'ECAPFM'
left outer join VW_ASR_STAFF k on
    a.SUID = k.SUID and b.STAFFCODE = k.STAFFCODE
order by b.DESCRIPTIONCODE, b.DESCRIPTIONSTR, d.NAATYPE, d.NAACODE, a.SCHLVL,
    a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO, b.ANPDATE, b.ANPRECID

 

如果要分學期抽取資料,又如何 ?

 

還有如沒有任何獎勵都要顯示項目

例 :

 

班別  學號  學生姓名     項目   大功  小功  優點  負責老師

6A        2    陳小明       風紀     0     0       0     王

6A      15    陳大明       風紀     0     0       0     王

班別  學號  學生姓名     項目   大功  小功  優點  負責老師

6A      2    陳小明      手球隊     0     0       1     吳

6B    15    王大明      手球隊     0     0       0    吳

 

已經是沒任何獎勵都會顯示。

select
    a.SCHYEAR '學年',
    a.CLASSLVL '級別',
    c.CLASSNAME '班別',
    a.CLASSNO '班號',
    a.CHNAME '中文姓名',
    dateformat(b.ANPDATE, 'DD/MM/YYYY') '獎勵日期',
    trim((case when trim(b.PREFIX) <> ''
         then trim(b.PREFIX)
         else ''
         end) +
        (case when trim(b.PREFIX) <> '' and
             (right(trim(b.PREFIX),1) between char(33) and char(126) or
             left(trim(b.DESCRIPTIONSTR),1) between char(33) and char(126) or
             left(trim(g.CH_DES),1) between char(33) and char(126))
         then ' '
         else ''
         end) +     
        (case when f.TOTAL > 0
         then (case when f.NONATTTYPE = 'ABSNT'
               then '缺席' + cast(cast(f.TOTAL as numeric(10,1)) as text) + '天'
               else '遲到' + cast(f.TOTAL as text) + '次'
               end)
         else (case when b.NAAOFFEREDID > 0
               then (case when e.NAAPOSTCODE is null and e.NAAPERFCODE is null
                     then h.CH_DES
                     else (case when e.NAAPOSTCODE is null or e.NAAPERFCODE is null
                           then (case when e.NAAPERFCODE is null
                                 then h.CH_DES + i.CH_DES
                                 else h.CH_DES + ' (' + j.CH_DES + ')'    
                                 end)
                           else h.CH_DES + i.CH_DES + ' (' + j.CH_DES + ')'
                           end)
                     end)
               else (case when trim(b.DESCRIPTIONSTR) <> ''
                     then b.DESCRIPTIONSTR
                     else g.CH_DES
                     end)
               end)
         end) +
        (case when trim(b.POSITION) <> '' and
             (right(trim(b.DESCRIPTIONSTR),1) between char(33) and char(126) or
             right(trim(g.CH_DES),1) between char(33) and char(126) or
             left(trim(b.POSITION),1) between char(33) and char(126))
         then ' '
         else ''
         end) +  
        (case when trim(b.POSITION) <> ''
         then trim(b.POSITION)
         else ''
         end)) '項目',
    b.LVL3 '大功',
    b.LVL2 '小功',
    b.LVL1 '優點',
    k.NAME_CHI '負責老師'
from VW_STU_LATESTSTUDENT a
join TB_STU_ANPSTUREC b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and
       b.ANPTYPE = 'A' and a.SCHYEAR = ?
join TB_SCH_TERM m
    on a.SUID = m.SUID and a.SCHYEAR = m.SCHYEAR and a.SCHLVL = m.SCHLEVEL and
       a.SCHSESS = m.SCHSESSION and a.CLASSLVL = m.CLASSLEVEL and
       b.ANPDATE >= m.TERMSTARTDATE and b.ANPDATE <= m.TERMENDDATE and
       m.TERMNO = ?
join TB_SCH_SCHCLASS c
    on a.SUID = c.SUID and a.SCHYEAR = c.SCHYEAR and a.SCHLVL = c.SCHLEVEL and
       a.SCHSESS = c.SCHSESSION and a.CLASSLVL = c.CLASSLEVEL and
       a.CLASSCODE = c.CLASSCODE
left outer join TB_NAA_NAAOFFERED d
    on a.SUID = d.SUID and b.SCHYEAR = d.SCHYEAR and b.NAAOFFEREDID = d.NAAOFFEREDID
left outer join TB_NAA_STUDENTNAA e
    on a.SUID = e.SUID and a.STUID = e.STUID and d.NAAOFFEREDID = e.NAAOFFEREDID
left outer join
    (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE,
         NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
     from TB_ATT_NONATT
     where ANPRECID is not null
     group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) f
    on a.SUID = f.SUID and b.SCHYEAR = f.SCHYEAR and a.STUID = f.STUID and
       b.ANPRECID = f.ANPRECID
left outer join TB_HSE_COMMON g
    on a.SUID = g.SUID and b.DESCRIPTIONCODE = g.CODE_ID and g.TB_ID = 'PRIZE'
left outer join wsadmin.TB_HSE_COMMON h on
    a.SUID = h.SUID and d.NAACODE = h.CODE_ID and
    ((h.TB_ID = 'ECACD' and d.NAATYPE = 'E') or
    (h.TB_ID = 'INTERSCH' and d.NAATYPE = 'I') or
    (h.TB_ID = 'SEVPST' and d.NAATYPE = 'S'))
left outer join TB_HSE_COMMON i on
    a.SUID = i.SUID and e.NAAPOSTCODE = i.CODE_ID and i.TB_ID = 'ECAPST'
left outer join TB_HSE_COMMON j on
    a.SUID = j.SUID and e.NAAPERFCODE = j.CODE_ID and j.TB_ID = 'ECAPFM'
left outer join VW_ASR_STAFF k on
    a.SUID = k.SUID and b.STAFFCODE = k.STAFFCODE
order by b.DESCRIPTIONCODE, b.DESCRIPTIONSTR, d.NAATYPE, d.NAACODE, a.SCHLVL,
    a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO, b.ANPDATE, b.ANPRECID

 

m.TERMNO 要入什麼？

試過入１，１１０１　都唔得

 

TERMNO 應該輸入1,2...。這個欄位是從「學校管理 > 校曆編修 > 校曆設定 > 學期資料 」中找出來的。

如果貴校有獎項的「日期」(我是指編修獎懲紀錄版面中稱為「日期」的欄位)是在本學年第一個學期內，閣下分別輸入 2007 和 1，應該可找出紀錄的。不少學校實際上是在第二個學期開始後才輸入第一個學期的獎懲資料，如果貴校也是這樣的話，那麼，相信難以使用任何方式區別不同學期的獎懲。

 

仍然未能做到沒有任何獎勵都要顯示項目

 

例 :

6A      2    陳小明      手球隊     0     0       1     吳

6B    15    王大明      手球隊     0     0       0    吳

 

 

<FONT color=#006600>腦人家 君：<BR><BR>請問，「6B 15&nbsp;王大明&nbsp;手球隊&nbsp;0&nbsp;0&nbsp;0&nbsp;吳」背後是否已經有一個獎勵紀錄存在？閣下是否需要在背後沒有獎勵紀錄的情況下，仍要顯示該學生曾參加過某些課外活動？<BR><BR>系統及資訊管理組</FONT>

 

是的 , 就算該生沒有獎勵但都要顯示他參加過那項目

thx

 

腦人家 君：

沒有奬勵紀錄的話，就不會有授獎教師資料，﹝如「6B 15 王大明 手球隊 0 0 0 吳」中的「吳」﹞。而且要留意，閣下所得的資料就不算是「獎懲紀錄」，因為背後確實並沒有獎懲紀錄。

系統及資訊管理組

 

我知道那些學生是沒有獎懲的

但當課外活動的資料到獎懲時,再依課外活動編修便可輸入優點,而負責課外活動的老師想核對他的輸入資料是否正確,無論學生是否有優點都想查看參加其課外活動的學生名單及給予的獎懲,故想用sql提取,以分派資料給各老師核對

 

趕住要d data,有無人可以答我呀 ?

thx

 

有關課外活動的 SQL 可以參考<a href="http://www.hkedcity.net/teachernet/sharing/forum/read.phtml?forum_id=52&current_page=2&tid=0&i=817437">這段討論</a>。

 

得左 , thx

 

如果想抽人手輸入項目的<FONT color=#000000>&#22892;勵紀錄，又怎辨？(每學生，所有年份)。謝謝！</FONT>

 

以下sql 十分有用，但欠缺了自入項目。請問高手如何修改。謝謝！

 

select l.SCHYEAR '學年', l.CLASSCODE '班別', l.CLASSNO '班號', b.CHNAME '中文姓名', b.ENNAME '英文姓名', (case when e.TOTAL > 0 then (case when e.NONATTTYPE = 'ABSNT' then '缺席' + cast(e.TOTAL as text) + '天' else '遲到' + cast(e.TOTAL as text) + '天' end) else (case when a.NAAOFFEREDID > 0 then (case when d.NAAPOSTCODE is null and d.NAAPERFCODE is null then g.CH_DES else (case when d.NAAPOSTCODE is null or d.NAAPERFCODE is null then (case when d.NAAPERFCODE is null then g.CH_DES + ' ' + h.CH_DES else g.CH_DES + ' (' + i.CH_DES + ')' end) else g.CH_DES + ' ' + h.CH_DES + ' (' + i.CH_DES + ')' end) end) else (case when trim(a.DESCRIPTIONSTR) <> '' then a.DESCRIPTIONSTR else f.CH_DES end) end) end)'獎勵事件名稱', a.POSITION '獎勵名次', m.SCHYEAR '獲獎學年', dateformat(a.ANPDATE, 'DD/MM/YYYY') '獲獎日期', (case when a.LVL5 > 0 then (case when a.ANPTYPE = 'A' then j.AWD5CHIDESC + cast(a.LVL5 as text) + '個' else j.PUNISH5CHIDESC + cast(a.LVL5 as text) + '個' end) else '' end) + (case when a.LVL4 > 0 then (case when a.ANPTYPE = 'A' then j.AWD4CHIDESC + cast(a.LVL4 as text) + '個' else j.PUNISH4CHIDESC + cast(a.LVL4 as text) + '個' end) else '' end) + (case when a.LVL3 > 0 then (case when a.ANPTYPE = 'A' then j.AWD3CHIDESC + cast(a.LVL3 as text) + '個' else j.PUNISH3CHIDESC + cast(a.LVL3 as text) + '個' end) else '' end) + (case when a.LVL2 > 0 then (case when a.ANPTYPE = 'A' then j.AWD2CHIDESC + cast(a.LVL2 as text) + '個' else j.PUNISH2CHIDESC + cast(a.LVL2 as text) + '個' end) else '' end) + (case when a.LVL1 > 0 then (case when a.ANPTYPE = 'A' then j.AWD1CHIDESC + cast(a.LVL1 as text) + '個' else j.PUNISH1CHIDESC + cast(a.LVL1 as text) + '個' end) else '' end) '獎懲數目', (case when a.PRINTIND = 1 then '是' else '否' end) '可印在成績表' from TB_STU_ANPSTUREC a left outer join TB_STU_STUDENT b on a.SUID = b.SUID and a.STUID = b.STUID left outer join TB_STU_STUSCHREC l on a.SUID = l.SUID and a.STUID = l.STUID left outer join TB_NAA_NAAOFFERED c on a.SUID = c.SUID and a.SCHYEAR = c.SCHYEAR and a.NAAOFFEREDID = c.NAAOFFEREDID left outer join TB_NAA_STUDENTNAA d on a.SUID = d.SUID and a.STUID = d.STUID and a.NAAOFFEREDID = d.NAAOFFEREDID left outer join (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT, sum(NONATTCOUNT)'TOTAL' from TB_ATT_NONATT where ANPRECID is not null group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) e on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.STUID = e.STUID and a.ANPRECID = e.ANPRECID left outer join TB_HSE_COMMON f on a.SUID = f.SUID and a.DESCRIPTIONCODE = f.CODE_ID and ((a.ANPTYPE = 'A' and f.TB_ID = 'PRIZE') or (a.ANPTYPE = 'P' and f.TB_ID = 'PSHMNT')) left outer join TB_HSE_COMMON g on a.SUID = g.SUID and c.NAACODE = g.CODE_ID and g.TB_ID = 'ECACD' left outer join TB_HSE_COMMON h on a.SUID = h.SUID and d.NAAPOSTCODE = h.CODE_ID and h.TB_ID = 'ECAPST' left outer join TB_HSE_COMMON i on a.SUID = i.SUID and d.NAAPERFCODE = i.CODE_ID and i.TB_ID = 'ECAPFM' left outer join TB_STU_ANPSCHEMEDETAILMD j on a.SUID = j.SUID and a.SCHYEAR = j.SCHYEAR left outer join TB_SCH_CALFORMAT m on a.SUID = m.SUID and a.ANPDATE >= m.SCHSTARTDATE and a.ANPDATE <= m.SCHENDDATE where l.SCHYEAR = ? and l.CLASSLVL = ? order by l.SCHSESS, l.CLASSCODE, l.CLASSNO, a.ANPTYPE, a.ANPDATE, a.DESCRIPTIONCODE, a.ANPRECID

 

閣下說到的「人手輸入項目」是指甚麼？在 WebSAMS 哪裏輸入？

 

Sorry, 應該是由 service and duty 所輸入的(項目)沒有顯示,功過就有顯示。

 

ck sir:

你所貼的 SQL 只提取獎懲的紀錄，至於活動或服務的紀錄，請參考這段討論。