SQL 抽取考勤資料

本文由 tr-elsachoi 在 2021-07-06 發表於 "WebSAMS 討論區" 討論區

  1. 55883951

    tr-elsachoi
    Expand Collapse

    文章:
    4
    讚:
    0
    select a.schyear '學年', a.CLASSCODE '班別', a.CLASSNO '班號', a.CHNAME '學生姓名(中)', a.ENNAME '學生姓名(英)', (select sum(c1.sysscore) from TB_ASR_SUBJASSESSDATA c1 where a.SUID = c1.SUID and a.STUID = c1.STUID and a.SCHYEAR = c1.SCHYEAR and a.SCHLVL = c1.SCHLEVEL and a.SCHSESS = c1.SCHSESSION and a.CLASSLVL = c1.CLASSLEVEL and c1.TIMESEQ = 1000) '全年績總分', b.omclasslvl '級名次', b.omclass '班名次', b.syspercscore '總平均分', b.OVERCONDGRADECONVERSIONCOMPCODE '操行', (select sum(c.NONATTCOUNT)'TOTAL'from TB_ATT_NONATT c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.ANPRECID is not null and c.NONATTTYPE = 'LATE' group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) '遲到', (select sum(c.NONATTCOUNT)'TOTAL'from TB_ATT_NONATT c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.ANPRECID is not null and c.NONATTTYPE = 'ABSNT' group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) '缺席', (select sum(c.lvl3) from TB_STU_ANPSTUREC c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.anptype = 'A') '大功', (select sum(c.lvl2) from TB_STU_ANPSTUREC c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.anptype = 'A') '小功', (select sum(c.lvl1) from TB_STU_ANPSTUREC c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.anptype = 'A') '優點', (select sum(d.lvl3) from TB_STU_ANPSTUREC d where a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.anptype = 'P') '大過', (select sum(d.lvl2) from TB_STU_ANPSTUREC d where a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.anptype = 'P') '小過', (select sum(d.lvl1) from TB_STU_ANPSTUREC d where a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.anptype = 'P') '缺點' from wsadmin.VW_STU_LATESTSTUDENT a left outer join TB_ASR_STUDASSESSDATA b on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and b.TIMESEQ = 1000 where a.schyear = ? and a.classlvl=? group by a.SCHYEAR, a.SUID, a.STUID, a.CLASSLVL, a.CLASSCODE, a.CLASSNO, a.SCHLVL, a.SCHSESS, a.CHNAME, a.ENNAME, b.omclasslvl, b.omclass, b.syspercscore, b.OVERCONDGRADECONVERSIONCOMPCODE order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO

    只能run到S1至S3, 但到S4同S5 就出了以下子句:
    SQL 錯誤: Exception:SQL Anywhere Error -186: Subquery cannot return more than one row
     
    #1 tr-elsachoi, 2021-07-06
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    211
    讚:
    0
    你好,

    這代表SUBQUERY有多過一個RESULT,即是出了2行RESULT。

    建議你嘗試逐步刪除SUBQUERY的部份,即是highlight顏色的subquery,找出那一個SUBQUERY出現2行RESULT。


    select a.schyear '學年', a.CLASSCODE '班別', a.CLASSNO '班號', a.CHNAME '學生姓名(中)', a.ENNAME '學生姓名(英)',
    (select sum(c1.sysscore) from wsadmin.TB_ASR_SUBJASSESSDATA c1
    where a.SUID = c1.SUID and a.STUID = c1.STUID and a.SCHYEAR = c1.SCHYEAR
    and a.SCHLVL = c1.SCHLEVEL and a.SCHSESS = c1.SCHSESSION and a.CLASSLVL = c1.CLASSLEVEL and c1.TIMESEQ = 1000) '全年績總分',

    b.omclasslvl '級名次', b.omclass '班名次', b.syspercscore '總平均分', b.OVERCONDGRADECONVERSIONCOMPCODE '操行',
    (select sum(c.NONATTCOUNT)'TOTAL'from wsadmin.TB_ATT_NONATT c
    where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR
    and c.ANPRECID is not null and c.NONATTTYPE = 'LATE' group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) '遲到',

    (select sum(c.NONATTCOUNT)'TOTAL'from wsadmin.TB_ATT_NONATT c
    where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.ANPRECID is not null and c.NONATTTYPE = 'ABSNT'
    group by c.SUID, c.SCHYEAR, c.STUID, c.ANPRECID, c.NONATTTYPE, c.NONATTCOUNT) '缺席',

    (select sum(c.lvl3) from wsadmin.TB_STU_ANPSTUREC c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.anptype = 'A') '大功',
    (select sum(c.lvl2) from wsadmin.TB_STU_ANPSTUREC c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.anptype = 'A') '小功',
    (select sum(c.lvl1) from wsadmin.TB_STU_ANPSTUREC c where a.SUID = c.SUID and a.STUID = c.STUID and a.SCHYEAR = c.SCHYEAR and c.anptype = 'A') '優點',
    (select sum(d.lvl3) from wsadmin.TB_STU_ANPSTUREC d where a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.anptype = 'P') '大過',
    (select sum(d.lvl2) from wsadmin.TB_STU_ANPSTUREC d where a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.anptype = 'P') '小過',
    (select sum(d.lvl1) from wsadmin.TB_STU_ANPSTUREC d where a.SUID = d.SUID and a.STUID = d.STUID and a.SCHYEAR = d.SCHYEAR and d.anptype = 'P') '缺點'

    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join wsadmin.TB_ASR_STUDASSESSDATA b on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and b.TIMESEQ = 1000
    where a.schyear = ? and a.classlvl= ?
    group by a.SCHYEAR, a.SUID, a.STUID, a.CLASSLVL, a.CLASSCODE, a.CLASSNO, a.SCHLVL, a.SCHSESS, a.CHNAME, a.ENNAME,
    b.omclasslvl, b.omclass, b.syspercscore, b.OVERCONDGRADECONVERSIONCOMPCODE
    order by a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-catherinewschan, 2021-07-06