none
优化 T_SQL RRS feed

  • 问题

  • SELECT
        NDCO,
        NDPOLY AS NBPOLY,
        NDNAMT AS NBPRMM,
                (CASE WHEN NDPPFL = 'S' THEN 'S' ELSE 'F' END)         AS NBPPFL,
         CAST(CAST(NDCRTY * 10000 + NDCRTM * 100 + NDCRTD AS CHAR) AS DATETIME)AS SDATE,
         MCPDTY * 10000 + MCPDTM * 100 + MCPDTD AS PTD 
                    FROM      S06E7C15.S06E7C15.LSMODDTA.NBSNDBNKD AS T1
       LEFT OUTER JOIN        S06E7C15.S06E7C15.LSPDTA.CASCNTRM AS T2
                   ON         T1.NDPOLY = T2.MCCNTR 
       UNION ALL 
     SELECT
         T3.APPCO,
         T3.APPPOL,
         T3.APPGAM,      
               (CASE WHEN T3.APPBRF = '00' THEN 'S' ELSE 'F' END) AS APPPFL,
               DATEADD(DAY, 1, CAST(CAST(T3.APPGFD AS CHAR) AS DATETIME)) AS SDATE,             
              T4.MCPDTY * 10000 + T4.MCPDTM * 100 + T4.MCPDTD AS PTD 
                  FROM        S06E7C15.S06E7C15.LSMODDTA.APPFILE AS T3
         LEFT OUTER JOIN      S06E7C15.S06E7C15.LSPDTA.CASCNTRM AS T4
                 ON        T3.APPPOL = T4.MCCNTR  WHERE (APPGFD > 0)

    看看有什么办法优化一下:

    还有:

    CAST(CAST(NDCRTY * 10000 + NDCRTM * 100 + NDCRTD AS CHAR) AS DATETIME)AS SDATE

    这个结果,看看怎们能变通一下

    NDCRTY NDCRTM NDCRTD      SDATE
    2007       1          15            2007-01-15 00:00:00.000 

    MCPDTY * 10000 + MCPDTM * 100 + MCPDTD AS PTD

    PTD
    20101125

    谢谢


    星光总能为我指引方向
    2011年5月24日 7:31

全部回复