SQL 抽取 學生科目評語 (Subject Comment)

本文由 abcdc 在 2012-03-05 發表於 "WebSAMS 討論區" 討論區

  1. 55173395

    abcdc
    Expand Collapse

    文章:
    15
    讚:
    0
    想要一條依學生抽取 學生科目評語 (Subject Comment) 既SQL,請問大家有冇一條可以 Share 下俾我。
    Thanks in advance!
     
  2. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    老師請參考以下的 SQL 語句。

    select
    a.CLASSLVL,
    a.CLASSCODE,
    a.CLASSNO,
    a.CHNAME,
    a.enNAME,
    d.CH_DES,
    d.en_DES,
    c.COMMENTDESCCHI,
    c.COMMENTDESCENG
    from wsadmin.TB_ASR_SUBJASSESSDATA c
    join wsadmin.TB_HSE_COMMON d
    on d.SUID = c.SUID and c.SCHYEAR = ? and c.TIMESEQ = ? and d.TB_ID = 'SBJ' and c.SUBJCODE = d.CODE_ID and c.CLASSLEVEL = ?
    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, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #2 edb-石頭, 2012-03-05
  3. 56424333

    edb-stsim2
    Expand Collapse

    文章:
    258
    讚:
    0
    select
    d.CLASSCODE 'Class',
    d.CLASSNO 'Class No.',
    d.chname 'Chinese Name',
    b.CH_des 'Subject',
    c.namechi 'Remark (Chi)',
    c.nameeng 'Remark (Eng)',
    a.timeseq,
    a.SUBJREMARKGRADECOMPCODE 'Grade'

    from tb_asr_subjremarkdata a
    left outer join tb_hse_common b
    on b.suid = a.suid and b.tb_id = 'SBJ' and b.code_id = a.subjcode
    left outer join tb_ASR_CLSLVLSUBJREMARK c
    on b.suid = a.suid and a.classlevel=c.classlevel and a.subjremarkseq=c.subjremarkseq
    left outer join VW_STU_LATESTSTUDENT d
    on a.suid=d.suid and a.stuid=d.stuid and a.schyear=d.schyear
    where a.schyear=? and a.classlevel=? and a.timeseq=?
    order by 1,2, b.code_id, a.subjremarkseq
     
    #3 edb-stsim2, 2012-03-05
  4. 56424333

    edb-stsim2
    Expand Collapse

    文章:
    258
    讚:
    0
    補充:我的 SQL 是 Subject Remarks 而不是 Subject Comments
     
    #4 edb-stsim2, 2012-03-06