SQL WebSAMS CDR內現特設【常用SQL參考庫】

本文由 nathanrd 在 2012-11-14 發表於 "WebSAMS 討論區" 討論區

  1. 57299120

    nathanrd
    Expand Collapse

    文章:
    70
    讚:
    0
    本帖最後由 藤井樹 於 2013-7-17 14:11 編輯

    為方便老師日常從WebSAMS Database內抽取相關資料之目的,由2012年11月15日起,在WebSAMS中央文件庫(CDR)內特設【常用SQL參考庫】,內有老師常用到的SQL十一個範疇:
    1. 學生資料 (Student info.)
    2. 學生成績 (Student assessment)
    3. 學生出席資料 (Student attendance)
    4. 學生課外活動 (Student activities)
    5. 教職員 (Staff info.)
    6. 學生學習概覽及其他學習經歷 (SLP & OLE)
    7. 香港學科測驗 (HKAT)
    8. 學生資助辦事處 (SFAA)
    9. 特殊學生成績 (Special Assessment)
    10. 獎懲資料 (Award & Punishment)
    11. 時間表編排 (Timetabling)
    12. 香港考評局中學文憑試 (HKDSE) **NEW**

    最後更新日期: 2013年7月17日


    網址為:常用SQL參考庫
    另設整個SQL的提問與答案的ZIP FILE: 瀏覽附件SQL - 11 modules_14_12_2012.zip
    此參考庫會定期更新,如對此參考庫有任何意見,歡迎老師在此WebSAMS 討論區 內提出。
     

    附件文件:

    • 123.GIF
      文件大小:
      58.8 KB
      瀏覽:
      4,704
    #1 nathanrd, 2012-11-14
    Last edited by a moderator: 2018-06-25
  2. Expand Collapse

    這個資料庫,實在太有用啦!
     
  3. 55444211

    恩sir
    Expand Collapse

    文章:
    20
    讚:
    0
    很有用,感謝各位無私分享
    小弟想使用學生成績28號....但連結失敗....
     
  4. 57299120

    nathanrd
    Expand Collapse

    文章:
    70
    讚:
    0
    #4 nathanrd, 2012-12-24
  5. 大茶煲

    大茶煲
    Expand Collapse

    這資料庫實在是大大的幫忙﹗

    想問一下sql中提到的parameter,我可以在哪兒找到相關的資料呢?
     
    #5 大茶煲, 2013-01-16
  6. 10004315

    chautc
    Expand Collapse

    文章:
    58
    讚:
    0
    下列SQL在websams 執行時出現問題
    The request may contains malicious code.
    Please contact the system administrator.[​IMG]

    select a.CLASSLVL, s.CLASSNAME, a.CLASSNO, a.ENNAME, a.CHNAME
    from VW_STU_LATESTSTUDENT a

    left outer join TB_SCH_SCHCLASS s
    on s.SUID=a.SUID and s.SCHYEAR=a.SCHYEAR and
    s.SCHLEVEL=a.SCHLVL and s.SCHSESSION=a.SCHSESS and
    s.CLASSLEVEL=a.CLASSLVL and s.CLASSCODE=a.CLASSCODE

    left outer join
    (SELECT n.suid, n.stuid, SUM(n.nonattcount) NONATTSUM
    from TB_ATT_NONATT n

    join TB_HSE_COMMON g
    on g.SUID=n.SUID and g.CODE_ID=n.NONATTCODE and
    ((n.NONATTTYPE='ABSNT' and g.TB_ID='SCHABS' and g.FLAG_3=0) or
    (n.NONATTTYPE='LATE' and g.TB_ID='SCHLAT' and g.FLAG_1=0))

    where dateformat(n.nonattdate, 'YYYY-MM-DD') >= ? and
    dateformat(n.nonattdate, 'YYYY-MM-DD') <= ?

    group by n.suid, n.stuid
    ) c on c.SUID=a.SUID and c.STUID=a.STUID

    where
    a.SCHYEAR=? and c.NONATTSUM is null

    order by a.SCHLVL, a.SCHSESS, a.CLASSLVL, s.CLASSNAME, a.CLASSNO
     
  7. 10004315

    chautc
    Expand Collapse

    文章:
    58
    讚:
    0
    下列SQL在websams 執行時出現問題
    The request may contains malicious code.
    Please contact the system administrator.[​IMG]

    select

    b.classname '班別',

    a.CLASSLVL '級別',

    a.classno '學號',

    a.chname '中文姓名',

    a.enname '英文姓名',

    a.regno '學生註冊編號',

    a.hkid '身份證號碼',

    a.pob '出生地點',

    dateformat(a.dob, 'DD/MM/YYYY') as dob,

    a.sex '性別',

    a.MOBILENO '學生手提電話',

    a.email '學生電郵',

    d.chname as 監護人姓名 ,

    d.OCCUPATION '監護人職業',

    a.hometel '住宅電話',

    d.EMERGENCYPHONE '緊急聯絡電話',

    (case when e.ch_des is not null then e.ch_des else '' end) +

    (case when trim(a.chdistrict)<>'' then trim(a.chdistrict) else '' end)+

    (case when trim(a.chstreet)<>'' then trim(a.chstreet) else '' end)+

    (case when trim(a.chvillageestate)<>'' then trim(a.chvillageestate) else '' end)+

    (case when trim(a.chbuilding)<>'' then trim(a.chbuilding) else '' end)+

    (case when trim(a.chblkno)<>'' then trim(a.chblkno)+'座' else '' end)+

    (case when trim(a.chfloorno)<>'' then trim(a.chfloorno)+'層' else '' end)+

    (case when trim(a.chflatno)<>'' then trim(a.chflatno)+'室' else '' end) as address

    from vw_stu_lateststudent a

    left outer join tb_sch_schclass b

    on b.classcode=a.classcode and

    b.schyear=a.schyear and

    b.suid = a.suid

    left outer join tb_hse_common c

    on c.code_id=a.schhouse and

    c.tb_id='SCHHUS' and

    c.suid = a.suid

    left outer join tb_stu_parent d

    on d.stuid = a.stuid and

    d.guardianind='1' and

    d.suid = a.suid

    left outer join tb_hse_common e

    on e.code_id=a.areacode and

    e.tb_id='AREACD' and

    e.suid = a.suid

    where a.schyear=?

    order by a.classcode, a.classno
     
  8. 57873296

    edb-rn
    Expand Collapse

    文章:
    231
    讚:
    0
    老師,請參考以下,

    有關 第一句SQL ,
    只需要把table 的alias a. 改為 z.
    就可以成功抽取資料。

    select
    z.CLASSLVL, s.CLASSNAME, z.CLASSNO, z.ENNAME, z.CHNAME
    from VW_STU_LATESTSTUDENT z
    left outer join TB_SCH_SCHCLASS s
    on s.SUID=z.SUID and s.SCHYEAR=z.SCHYEAR and
    s.SCHLEVEL=z.SCHLVL and s.SCHSESSION=z.SCHSESS and
    s.CLASSLEVEL=z.CLASSLVL and s.CLASSCODE=z.CLASSCODE
    left outer join
    (SELECT n.suid, n.stuid, SUM(n.nonattcount) NONATTSUM
    from TB_ATT_NONATT n
    join TB_HSE_COMMON g
    on g.SUID=n.SUID and g.CODE_ID=n.NONATTCODE and
    ((n.NONATTTYPE='ABSNT' and g.TB_ID='SCHABS' and g.FLAG_3=0) or
    (n.NONATTTYPE='LATE' and g.TB_ID='SCHLAT' and g.FLAG_1=0))
    where dateformat(n.nonattdate, 'YYYY-MM-DD') >= ? and
    dateformat(n.nonattdate, 'YYYY-MM-DD') <= ?
    group by n.suid, n.stuid
    ) c on c.SUID=z.SUID and c.STUID=z.STUID
    where
    z.SCHYEAR=? and c.NONATTSUM is null
    order by z.SCHLVL, z.SCHSESS, z.CLASSLVL, s.CLASSNAME, z.CLASSNO
     
  9. 57873296

    edb-rn
    Expand Collapse

    文章:
    231
    讚:
    0
    有關 第二句SQL ,
    只需要把table 的alias a. 改為 z.
    就可以成功抽取資料。

    select
    b.classname '班別',
    z.CLASSLVL '級別',
    z.classno '學號',
    z.chname '中文姓名',
    z.enname '英文姓名',
    z.regno '學生註冊編號',
    z.hkid '身份證號碼',
    z.pob '出生地點',
    dateformat(z.dob, 'DD/MM/YYYY') as dob,
    z.sex '性別',
    z.MOBILENO '學生手提電話',
    z.email '學生電郵',
    d.chname as '監護人姓名',
    d.OCCUPATION '監護人職業',
    z.hometel '住宅電話',
    d.EMERGENCYPHONE '緊急聯絡電話',
    (case when e.ch_des is not null then e.ch_des else '' end) +
    (case when trim(z.chdistrict)<>'' then trim(z.chdistrict) else '' end)+
    (case when trim(z.chstreet)<>'' then trim(z.chstreet) else '' end)+
    (case when trim(z.chvillageestate)<>'' then trim(z.chvillageestate) else '' end)+
    (case when trim(z.chbuilding)<>'' then trim(z.chbuilding) else '' end)+
    (case when trim(z.chblkno)<>'' then trim(z.chblkno)+'座' else '' end)+
    (case when trim(z.chfloorno)<>'' then trim(z.chfloorno)+'層' else '' end)+
    (case when trim(z.chflatno)<>'' then trim(z.chflatno)+'室' else '' end) as address
    from vw_stu_lateststudent z
    left outer join tb_sch_schclass b
    on b.classcode=z.classcode and
    b.schyear=z.schyear and
    b.suid = z.suid
    left outer join tb_hse_common c
    on c.code_id=z.schhouse and
    c.tb_id='SCHHUS' and
    c.suid = z.suid
    left outer join tb_stu_parent d
    on d.stuid = z.stuid and
    d.guardianind='1' and
    d.suid = z.suid
    left outer join tb_hse_common e
    on e.code_id=z.areacode and
    e.tb_id='AREACD' and
    e.suid = z.suid
    where z.schyear=?
    order by z.classcode, z.classno
     
  10. 10004315

    chautc
    Expand Collapse

    文章:
    58
    讚:
    0
    常用SQL參考庫
    ZIP FILE 欠缺

    12. 香港考評局中學文憑試 (HKDSE) **NEW**