SQL SQL 抽學生ABS 等總數

本文由 Hydrangea 在 2019-12-17 發表於 "WebSAMS 討論區" 討論區

  1. 55020416

    Hydrangea
    Expand Collapse

    文章:
    17
    讚:
    0
    寫了以下的SQL抽學生於某一日子內的ABS, LATE等總數, 之前work, 今日run 唔到, 有以下warning:
    The request may contains malicious code.
    Please contact the system administrator.


    SELECT a.stuid,
    STRING(classcode,'-',classno,'-',chname),
    STRING(nonatttype,'-',nonattcode),
    sum(nonattcount)
    FROM TB_ATT_NONATT a, TB_STU_STUDENT s
    WHERE schyear=2019 AND a.stuid=s.stuid and a.nonattdate >= ? and a.nonattdate <= ?
    GROUP BY a.stuid, classcode, classno, chname,
    nonatttype, nonattcode
    ORDER BY classcode, classno

    好可能紅色位出問題,因為唔要一個input可以run 的,但我要check 某一時段~
     
    #1 Hydrangea, 2019-12-17
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    你好, 可以把SQL的日期改用BETWEEN代替 >= 和 <=

    SELECT a.stuid,
    STRING(s.classcode,'-',s.classno,'-',s.chname),
    STRING(a.nonatttype,'-',a.nonattcode),
    sum(a.nonattcount)
    FROM wsadmin.TB_ATT_NONATT a
    left outer join wsadmin.TB_STU_STUDENT s on a.stuid=s.stuid
    WHERE a.schyear=2019 AND ( a.nonattdate between '2019-01-01' and '2019-12-01')
    GROUP BY a.stuid, s.classcode, s.classno, s.chname, a.nonatttype, a.nonattcode
    ORDER BY s.classcode, s.classno
     
    #2 edb-catherinewschan, 2019-12-18
  3. 55020416

    Hydrangea
    Expand Collapse

    文章:
    17
    讚:
    0
    多謝你回覆,因為我條sql會比office 同事用, 佢地可能因應不同需要extract data,有無方法好似以前咁比人入不同的parameter?
     
    #3 Hydrangea, 2019-12-18
  4. 55020416

    Hydrangea
    Expand Collapse

    文章:
    17
    讚:
    0
    補充, OFFICE同事加左入去:
    Group ID DM_USER

    所以佢地改唔到條SQL
     
    #4 Hydrangea, 2019-12-18
  5. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    你好,

    試驗過後,發現問題不是出於DATE RANGE, 而是GROUP BY。
    我把SQL改了,先把兩個資料表JOIN起, 之後才做GROUP BY 及 DATE FILTER。


    select
    T.stuid,
    T.student,
    T.typecode,
    sum(T.nonattcount) from (
    SELECT a.stuid,
    STRING(s.classcode,'-',s.classno,'-',s.chname) as student,
    STRING(a.nonatttype,'-',a.nonattcode) as typecode,
    a.nonattdate,
    a.nonattcount
    FROM wsadmin.TB_ATT_NONATT a
    join wsadmin.TB_STU_STUDENT s on a.stuid=s.stuid
    where a.schyear=?

    ) T
    where (T.nonattdate >=? and T.nonattdate<=?)
    GROUP BY T.stuid, T.student, T.typecode
    order by T.student
     
    #5 edb-catherinewschan, 2019-12-23