SQL SQL_抽取獎勵資料「後置補充」資料

本文由 Hello Wind 在 2022-01-06 發表於 "WebSAMS 討論區" 討論區

上一篇 下一篇
正在加載...
  1. 56955171

    Hello Wind
    Expand Collapse

    文章:
    173
    讚:
    0
    負責SQL之同工，
    你好，我校年中使用此Sql抽取資料予班主任評學生操行。今年應校方安求，使用了獎懲資料內的「後置補充」功能。如代碼文字(熱心服務) / 後置補充 (一百五十周年校慶陸運會)。現時只能抽取(熱心服務)，請問如何優化為抽出﹕(熱心服務) + (一百五十周年校慶陸運會)
    謝謝及附上原本之Sql如下﹕

    Select Distinct
    b.Schyear,
    '#'||b.REGNO "Regno",
    b.CLASSCODE 'Class',
    b.CLASSNO 'No',
    b.ENNAME 'EName',
    b.CHNAME 'Name',
    DATEFORMAT(a.ANPDATE, 'DD/MM/YYYY') 'DATE',
    g.CH_DES 'Prize',
    TSSD.CH_DES 'Activity',
    TSSE.CH_DES 'Post',
    h.CH_DES 'Punish',
    a.LVL3*9+a.LVL2*3+a.LVL1 'Lvl'


    FROM VW_STU_ANPSTUREC a

    JOIN VW_STU_LATESTSTUDENT b
    ON b.SUID = a.SUID AND
    b.STUID = a.STUID AND
    b.SCHYEAR = a.SCHYEAR

    left outer join vw_stu_lateststudent c1
    on c1.suid=b.suid and
    c1.stuid=b.stuid and
    c1.SCHYEAR=b.SCHYEAR

    LEFT OUTER JOIN TB_SCH_SCHOOL c
    ON c.SUID = a.SUID AND
    c.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN TB_STU_ANPSCHEMEDETAILMD d
    ON d.SUID = a.SUID AND
    d.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN TB_STU_ANPSCHEME e
    ON e.SUID = a.SUID AND
    e.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN TB_ATT_NONATT f
    ON f.SUID = a.SUID AND
    f.STUID = a.STUID AND
    f.ANPRECID = a.ANPRECID

    LEFT OUTER JOIN TB_HSE_COMMON g
    ON g.SUID = a.SUID AND
    g.CODE_ID = a.DESCRIPTIONCODE AND
    g.TB_ID = 'PRIZE' and a.ANPTYPE='A'

    LEFT OUTER JOIN TB_HSE_COMMON h
    ON h.SUID = a.SUID AND
    h.CODE_ID = a. DESCRIPTIONCODE AND
    h.TB_ID = 'PSHMNT' and a.ANPTYPE='P'

    LEFT OUTER JOIN VW_STU_LATESTSTUSCHREC i
    ON i.SUID = a.SUID AND
    i.STUID = a.STUID AND
    i.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN TB_SCH_SCHCLASS j
    ON j.SUID = i.SUID AND
    j.SCHYEAR = i.SCHYEAR AND
    j.SCHLEVEL = i.SCHLVL AND
    j.SCHSESSION = i.SCHSESS AND
    j.CLASSLEVEL = i.CLASSLVL AND
    j.CLASSCODE = i.CLASSCODE

    LEFT OUTER JOIN VW_NAA_CLASSNAA s
    ON a.suid = s.suid AND
    a.STUID = s.STUID AND
    a.schyear = s.schyear and
    a.NAAOFFEREDID=s.NAAOFFEREDID

    LEFT OUTER JOIN TB_SCH_SCHCLASS TSSA
    ON TSSA.SUID = S.SUID AND TSSA.SCHYEAR = S.SCHYEAR AND TSSA.SCHLEVEL = S.SCHLVL AND TSSA.SCHSESSION = S.SCHSESS AND TSSA.CLASSLEVEL = S.CLASSLVL AND TSSA.CLASSCODE = S.CLASSCODE

    LEFT OUTER JOIN TB_SCH_SCHOOL TSSB
    ON TSSB.SUID = A.SUID AND TSSB.SCHYEAR = A.SCHYEAR

    LEFT OUTER JOIN VW_NAA_NAATYPEDURATION TSSC
    ON TSSC.SUID = S.SUID AND
    TSSC.NAATYPE = S.NAATYPE AND
    TSSC.NAADURATION = S.DURATION and
    (TSSC.NAATYPE = 'E' or TSSC.NAATYPE = 'S')

    LEFT OUTER JOIN TB_HSE_COMMON TSSD
    ON TSSD.SUID = S.SUID AND
    TSSD.CODE_ID = S.NAACODE and
    (TSSD.TB_ID = 'ECACD' or TSSD.TB_ID = 'SEVPST') and
    TSSD.CODE_ST ='A'

    LEFT OUTER JOIN TB_HSE_COMMON TSSE
    ON TSSE.SUID = S.SUID AND
    TSSE.CODE_ID = S.NAAPOSTCODE and
    TSSE.TB_ID = 'ECAPST'


    WHERE a.SCHYEAR = 2021

    ORDER BY b.CLASSCODE, b.CLASSNO
     
    #1 Hello Wind, 2022-01-06
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    你好,

    加了 '前置補充' 和 '後置補充' ，請試試看

    Select Distinct
    b.Schyear,
    '#'||b.REGNO "Regno",
    b.CLASSCODE 'Class',
    b.CLASSNO 'No',
    b.ENNAME 'EName',
    b.CHNAME 'Name',
    DATEFORMAT(a.ANPDATE, 'DD/MM/YYYY') 'DATE',
    a.prefix '前置補充',
    g.CH_DES 'Prize',
    a.POSITION '後置補充',
    TSSD.CH_DES 'Activity',
    TSSE.CH_DES 'Post',
    h.CH_DES 'Punish',
    a.LVL3*9+a.LVL2*3+a.LVL1 'Lvl'


    FROM wsadmin.VW_STU_ANPSTUREC a

    JOIN wsadmin.VW_STU_LATESTSTUDENT b
    ON b.SUID = a.SUID AND
    b.STUID = a.STUID AND
    b.SCHYEAR = a.SCHYEAR

    left outer join wsadmin.vw_stu_lateststudent c1
    on c1.suid=b.suid and
    c1.stuid=b.stuid and
    c1.SCHYEAR=b.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL c
    ON c.SUID = a.SUID AND
    c.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEMEDETAILMD d
    ON d.SUID = a.SUID AND
    d.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEME e
    ON e.SUID = a.SUID AND
    e.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_ATT_NONATT f
    ON f.SUID = a.SUID AND
    f.STUID = a.STUID AND
    f.ANPRECID = a.ANPRECID

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON g
    ON g.SUID = a.SUID AND
    g.CODE_ID = a.DESCRIPTIONCODE AND
    g.TB_ID = 'PRIZE' and a.ANPTYPE='A'

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON h
    ON h.SUID = a.SUID AND
    h.CODE_ID = a. DESCRIPTIONCODE AND
    h.TB_ID = 'PSHMNT' and a.ANPTYPE='P'

    LEFT OUTER JOIN wsadmin.VW_STU_LATESTSTUSCHREC i
    ON i.SUID = a.SUID AND
    i.STUID = a.STUID AND
    i.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS j
    ON j.SUID = i.SUID AND
    j.SCHYEAR = i.SCHYEAR AND
    j.SCHLEVEL = i.SCHLVL AND
    j.SCHSESSION = i.SCHSESS AND
    j.CLASSLEVEL = i.CLASSLVL AND
    j.CLASSCODE = i.CLASSCODE

    LEFT OUTER JOIN wsadmin.VW_NAA_CLASSNAA s
    ON a.suid = s.suid AND
    a.STUID = s.STUID AND
    a.schyear = s.schyear and
    a.NAAOFFEREDID=s.NAAOFFEREDID

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS TSSA
    ON TSSA.SUID = S.SUID AND TSSA.SCHYEAR = S.SCHYEAR AND TSSA.SCHLEVEL = S.SCHLVL AND TSSA.SCHSESSION = S.SCHSESS AND TSSA.CLASSLEVEL = S.CLASSLVL AND TSSA.CLASSCODE = S.CLASSCODE

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL TSSB
    ON TSSB.SUID = A.SUID AND TSSB.SCHYEAR = A.SCHYEAR

    LEFT OUTER JOIN wsadmin.VW_NAA_NAATYPEDURATION TSSC
    ON TSSC.SUID = S.SUID AND
    TSSC.NAATYPE = S.NAATYPE AND
    TSSC.NAADURATION = S.DURATION and
    (TSSC.NAATYPE = 'E' or TSSC.NAATYPE = 'S')

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSD
    ON TSSD.SUID = S.SUID AND
    TSSD.CODE_ID = S.NAACODE and
    (TSSD.TB_ID = 'ECACD' or TSSD.TB_ID = 'SEVPST') and
    TSSD.CODE_ST ='A'

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSE
    ON TSSE.SUID = S.SUID AND
    TSSE.CODE_ID = S.NAAPOSTCODE and
    TSSE.TB_ID = 'ECAPST'


    WHERE a.SCHYEAR = ?

    ORDER BY b.CLASSCODE, b.CLASSNO
     
    #2 edb-catherinewschan, 2022-01-06
  3. 56955171

    Hello Wind
    Expand Collapse

    文章:
    173
    讚:
    0
    感謝edb-catherinewschan快捷的回覆
    發覺此sql抽出來的是「事件(英文)」內之「前置/後置補充」資料
    而早前同事輸入的是「事件(中文)」內之「前置/後置補充」資料
    請問如何可抽出事件(中文)之補充資料 謝謝!
     
    #3 Hello Wind, 2022-01-07
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    250
    讚:
    0
    你好,

    已加入中文的前置/後置補充, 請看看

    Select Distinct
    b.Schyear,
    '#'||b.REGNO "Regno",
    b.CLASSCODE 'Class',
    b.CLASSNO 'No',
    b.ENNAME 'EName',
    b.CHNAME 'Name',
    DATEFORMAT(a.ANPDATE, 'DD/MM/YYYY') 'DATE',
    a.prefix '前置補充Eng',
    oth_antecedence '前置補充Chi',
    g.CH_DES 'Prize',
    a.POSITION '後置補充Eng',
    oth_addendum '後置補充Chi',
    TSSD.CH_DES 'Activity',
    TSSE.CH_DES 'Post',
    h.CH_DES 'Punish',
    a.LVL3*9+a.LVL2*3+a.LVL1 'Lvl'


    FROM wsadmin.VW_STU_ANPSTUREC a

    JOIN wsadmin.VW_STU_LATESTSTUDENT b
    ON b.SUID = a.SUID AND
    b.STUID = a.STUID AND
    b.SCHYEAR = a.SCHYEAR

    left outer join wsadmin.vw_stu_lateststudent c1
    on c1.suid=b.suid and
    c1.stuid=b.stuid and
    c1.SCHYEAR=b.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL c
    ON c.SUID = a.SUID AND
    c.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEMEDETAILMD d
    ON d.SUID = a.SUID AND
    d.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_STU_ANPSCHEME e
    ON e.SUID = a.SUID AND
    e.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_ATT_NONATT f
    ON f.SUID = a.SUID AND
    f.STUID = a.STUID AND
    f.ANPRECID = a.ANPRECID

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON g
    ON g.SUID = a.SUID AND
    g.CODE_ID = a.DESCRIPTIONCODE AND
    g.TB_ID = 'PRIZE' and a.ANPTYPE='A'

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON h
    ON h.SUID = a.SUID AND
    h.CODE_ID = a. DESCRIPTIONCODE AND
    h.TB_ID = 'PSHMNT' and a.ANPTYPE='P'

    LEFT OUTER JOIN wsadmin.VW_STU_LATESTSTUSCHREC i
    ON i.SUID = a.SUID AND
    i.STUID = a.STUID AND
    i.SCHYEAR = a.SCHYEAR

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS j
    ON j.SUID = i.SUID AND
    j.SCHYEAR = i.SCHYEAR AND
    j.SCHLEVEL = i.SCHLVL AND
    j.SCHSESSION = i.SCHSESS AND
    j.CLASSLEVEL = i.CLASSLVL AND
    j.CLASSCODE = i.CLASSCODE

    LEFT OUTER JOIN wsadmin.VW_NAA_CLASSNAA s
    ON a.suid = s.suid AND
    a.STUID = s.STUID AND
    a.schyear = s.schyear and
    a.NAAOFFEREDID=s.NAAOFFEREDID

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS TSSA
    ON TSSA.SUID = S.SUID AND TSSA.SCHYEAR = S.SCHYEAR AND TSSA.SCHLEVEL = S.SCHLVL AND TSSA.SCHSESSION = S.SCHSESS AND TSSA.CLASSLEVEL = S.CLASSLVL AND TSSA.CLASSCODE = S.CLASSCODE

    LEFT OUTER JOIN wsadmin.TB_SCH_SCHOOL TSSB
    ON TSSB.SUID = A.SUID AND TSSB.SCHYEAR = A.SCHYEAR

    LEFT OUTER JOIN wsadmin.VW_NAA_NAATYPEDURATION TSSC
    ON TSSC.SUID = S.SUID AND
    TSSC.NAATYPE = S.NAATYPE AND
    TSSC.NAADURATION = S.DURATION and
    (TSSC.NAATYPE = 'E' or TSSC.NAATYPE = 'S')

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSD
    ON TSSD.SUID = S.SUID AND
    TSSD.CODE_ID = S.NAACODE and
    (TSSD.TB_ID = 'ECACD' or TSSD.TB_ID = 'SEVPST') and
    TSSD.CODE_ST ='A'

    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON TSSE
    ON TSSE.SUID = S.SUID AND
    TSSE.CODE_ID = S.NAAPOSTCODE and
    TSSE.TB_ID = 'ECAPST'


    WHERE a.SCHYEAR = ?

    ORDER BY b.CLASSCODE, b.CLASSNO
     
    #4 edb-catherinewschan, 2022-01-07
  5. 56955171

    Hello Wind
    Expand Collapse

    文章:
    173
    讚:
    0
    成功了 感謝!
     
    #5 Hello Wind, 2022-01-10
上一篇 下一篇
正在加載...
(您必須登入後才可以張貼。)