SQL 使用SQL匯出每科首末名次的學生名單

本文由 LO Yue Kwan Wallace 在 2020-06-16 發表於 "WebSAMS 討論區" 討論區

  1. 57670192

    LO Yue Kwan Wallace
    Expand Collapse

    文章:
    19
    讚:
    0
    請問有沒有SQL 範本,可以用作匯出以下資料:
    1. 每班每科的首末五名 (體育科分開男女學生計算)
    2. 每級每科的首末五名 (體育科分開男女學生計算)
    3. 每班每科的首名 (體育科分開男女學生計算)

    謝謝。
     
    #1 LO Yue Kwan Wallace, 2020-06-16
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    128
    讚:
    0
    你好, 可以到網上校管系統資料庫 -> 常用SQL參考庫 -> 學生成績 (下載)

    參考一下SQL , 把 TIMESEQ 的數值更改
    4. 抽取一個T1A1學期中一至中六全科頭五名。

    至於 (體育科分開男女學生計算) ,請問體育科的SUBJECT CODE 是什麼 ?
     
    #2 edb-catherinewschan, 2020-06-18
  3. 57670192

    LO Yue Kwan Wallace
    Expand Collapse

    文章:
    19
    讚:
    0
    我們現時體育科的CODE 是310
     
    #3 LO Yue Kwan Wallace, 2020-07-16
  4. 57670192

    LO Yue Kwan Wallace
    Expand Collapse

    文章:
    19
    讚:
    0
    另外我查看了你建議參考的資料,可以做到全級每科首五名的資料,但請問如何可以做到一條SQL 按級別抽取每班每科的首5名呢?謝謝。
     
    #4 LO Yue Kwan Wallace, 2020-07-16
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    128
    讚:
    0
    你好

    只要把 c.omclasslvl 改為 c.omclass ,並更改一些列印次序order就可以了。


    select
    c.CLASSLEVEL,
    d.CH_DES,
    a.CLASSCODE,
    a.CLASSNO,
    a.CHNAME,
    c.SYSSCORE,
    c.omclass
    from wsadmin.TB_ASR_SUBJASSESSDATA c
    join wsadmin.TB_HSE_COMMON d
    on d.SUID = c.SUID and c.SCHYEAR = ? and c.TIMESEQ = ? and c.omclass <=5 and d.TB_ID = 'SBJ' and c.SUBJCODE = d.CODE_ID and c.CLASSLEVEL = ?
    join wsadmin.TB_ASR_CLSLVLSUBJSETTING e
    on c.SUID = e.SUID and c.SCHYEAR = e.SCHYEAR and c.SCHLEVEL = e.SCHLEVEL and c.SCHSESSION = e.SCHSESSION and
    c.CLASSLEVEL = e.CLASSLEVEL and c.SUBJCODE = e.SUBJCODE and c.TIMESEQ = e.TIMESEQ and c.MOI = e.MOI and e.BYGRADEIND = 'N'
    join wsadmin.VW_STU_LATESTSTUDENT a
    on a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR
    order by a.SCHSESS, c.CLASSLEVEL, a.CLASSCODE, d.EN_DES, c.OMCLASSLVL, a.CLASSNO
     
    #5 edb-catherinewschan, 2020-07-17
    Last edited: 2020-07-17
  6. 57670192

    LO Yue Kwan Wallace
    Expand Collapse

    文章:
    19
    讚:
    0
    十分感謝教育局同工的支援,但另外我如何可以做到,將體育科的成績分開男女顯示各性別的首五名呢?謝謝。
     
    #6 LO Yue Kwan Wallace, 2020-07-23
  7. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    128
    讚:
    0
    你好,

    可以加入SUBJCODE = 310 的條件句

    請參考一下以下的SQL....

    select
    c.CLASSLEVEL,
    d.CH_DES,
    a.CLASSCODE,
    a.CLASSNO,
    a.CHNAME,
    c.SYSSCORE,
    c.omclass
    from wsadmin.TB_ASR_SUBJASSESSDATA c
    join wsadmin.TB_HSE_COMMON d
    on d.SUID = c.SUID and c.SCHYEAR = ? and c.TIMESEQ = ? and c.omclass <=5 and d.TB_ID = 'SBJ' and c.SUBJCODE = d.CODE_ID and c.CLASSLEVEL = ?
    join wsadmin.TB_ASR_CLSLVLSUBJSETTING e
    on c.SUID = e.SUID and c.SCHYEAR = e.SCHYEAR and c.SCHLEVEL = e.SCHLEVEL and c.SCHSESSION = e.SCHSESSION and
    c.CLASSLEVEL = e.CLASSLEVEL and c.SUBJCODE = e.SUBJCODE and c.TIMESEQ = e.TIMESEQ and c.MOI = e.MOI and e.BYGRADEIND = 'N'
    join wsadmin.VW_STU_LATESTSTUDENT a
    on a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR
    where c.SUBJCODE ='310'
    order by a.SCHSESS, c.CLASSLEVEL, a.CLASSCODE, d.EN_DES, c.OMCLASSLVL, a.CLASSNO
     
    #7 edb-catherinewschan, 2020-07-25