技巧 SQL分享

Ø 进线 SQL1

SELECT SUBSTR(C.STIME, 9, 2) || ':' || SUBSTR(C.STIME,11, 2) HHMM,

             NVL(B.CALLIN_QTY, 0) CALLIN_QTY

             FROM (SELECT TO_CHAR(TO_DATE(#REAL_END_TIME#, 'yyyy-mm-dd hh24:mi')

                      ((ROWNUM - 1) / 1440),

                      'yyyymmddhh24mi') AS STIME

          FROMTB_SYS_DEPT

         WHEREROWNUM <= #REAL_PLAN_TIME#) C,

       (SELECTCOUNT(1) CALLIN_QTY,

              TO_CHAR(A.CALL_DATE, 'yyyymmddhh24mi') STIME

          FROMTB_PGM_LIVE_CALLIN A

         WHEREA.PGM_ID = #PGM_ID#

           ANDA.LINE_FLAG IN (1, 2, 13, 18, 19) A.GOOD_ID

         IN($GOOD_ID$)

         GROUP BYTO_CHAR(A.CALL_DATE, 'yyyymmddhh24mi')) B

 WHERE C.STIME =B.STIME(+)

 ORDER BY C.STIME

Ø 进线 SQL2

SELECT TO_CHAR(B.ETIME,'hh24:mi') HHMM,COUNT(C.CALLIN_SEQ) CALLIN_QTY

FROM

    (SELECT sysdate  CTIME, sysdate-RowNum/(1440) STIME ,sysdate-(RowNum-1)/(1440) ETIME

     FROMTB_SYS_DEPT

     WHERE  ROWNUM<=41) B LEFT JOIN

    (

      SELECTA.CALLIN_SEQ,A.CALL_DATE CALL_DATE

      FROM  TB_PGM_LIVE_CALLIN A

      WHEREA.PGM_ID=#PGM_ID#

      ANDA.LINE_FLAG IN (1,2,13,18,19)

      A.GOOD_IDIN($GOOD_ID$)

     )  C

ON C.CALL_DATE>B.STIME AND C.CALL_DATE<=B.ETIME

GROUP BY TO_CHAR(B.ETIME,'hh24:mi')

ORDER BY TO_CHAR(B.ETIME,'hh24:mi')

Ø 商品搜索同时满足多个属性值条件查询

SELECT A.PROD_ID ,A.PROD_NAME,A.BRAND_NAME     

FROM TB_PROD_MAS A

EXISTS (SELECT 1 FROM TB_PROD_EXPL_VALUE  B

             WHERE A.PROD_ID=B.PROD_ID  and

               B.OPTION_SEQ IN($optionseq$)

             GROUP BY B.PROD_ID

             HAVING COUNT(1)=#explnum#

           )