Sqlserver中嘗試了一個開發(fā)的寫法

三水ssl適用于網站、小程序/APP、API接口等需要進行數據傳輸應用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18982081108（備注：SSL證書合作）期待與您的合作！

實現(xiàn)功能是掃描當前每條記錄時，把下一條記錄合并到當前行。

用自關聯(lián)CURR.RN = NEXT.RN +1(能找下一條)的方式查找時30萬的數據就很慢，甚至30分鐘后就內存溢出，最后調試后發(fā)現(xiàn)導致慢的根本原因是用表變量存儲了中間結果，然后從表變量里查詢數據時就非常慢。數據量如果很大都緩存到內存里，可能已經占用很多內存，后面再查詢時表自關聯(lián)時也要用到很多內存，所以就慢且最后內存溢出了。

解決方法就是把表變量換成臨時表，這樣查詢時有足夠內存可以使用，速度從30分鐘到10秒鐘。

DECLARE @DI_V2_BFGATE TABLE(                     

                     RN             NUMERIC(19,0)

                   , WORKDT          VARCHAR(20)

                   , IDNO            VARCHAR(20)

                   , INOUTTIME         VARCHAR(20)

                   , INOUTGBNCD           VARCHAR(20)

                   , IF_SQ             BIGINT

                   --, WKT_TOT_TM      NUMERIC(19,0)

                   );

     -- INSERT INTO @DI_V2_BFGATE

       SELECT

              ROW_NUMBER()OVER(PARTITION BY T.WORKDT,IDNO ORDER BY INOUTTIME) RN

              --ROW_NUMBER()OVER(ORDER BY IDNO,INOUTTIME) RN OLD WAY

             ,T.WORKDT

             --,T2.OVTM_DT

             --,T3.TMOFF_DT

             --,T3.TMOFF_NM

             --,T3.TMOFF_TYPE

             ,IDNO

             ,INOUTTIME

             ,INOUTGBNCD

             ,T.IF_SQ INTO #DI_V2_BFGATE

        FROM T_DI_V2_BFGATE T

        LEFT JOIN T_SI_GHR_OVTM T2 ON(

                                        T.IDNO = T2.EMP_ID

                                        AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T2.OVTM_DT,120),'-','')

                                        AND T2.TIME_WEEK_CD = '1'

                                        AND T2.SHIFT_TYPE = 'OFMW'

                                       )

        LEFT JOIN T_SI_GHR_TIME_OFF T3 ON(

                                        T.IDNO = T3.EMP_ID

                                        AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T3.TMOFF_DT,120),'-','')

                                        AND T3.TMOFF_TYPE IN('YC')

                                        )

        WHERE 1=1

     -- AND IDNO = '12587526' --test case

        AND  WORKDT >= '20170101' AND WORKDT < '20170201'

        AND IsNumeric(IDNO) = 1

        --(1)插入上午集中工作時間違反記錄

       DECLARE @WKT_TEMP TABLE(

                     WORKDT          VARCHAR(20)

                   , IDNO            VARCHAR(20)

                   , OUT_DT          DATETIME

                   , IN_DT           DATETIME

                   );

       INSERT INTO @WKT_TEMP

       SELECT WORKDT

             ,IDNO

             ,OUT_DT

             ,IN_DT

       FROM(

           SELECT T1.WORKDT

                  ,T1.IDNO

                  ,CAST(SUBSTRING(T1.INOUTTIME,0,9) AS DATE) WKT_DATE

                  ,CONVERT(DATETIME,SUBSTRING(LEFT(T1.INOUTTIME,8)+' ' + SUBSTRING(T1.INOUTTIME,9,2)+':' + SUBSTRING(T1.INOUTTIME,11,2)+':' + SUBSTRING(T1.INOUTTIME,13,2),1,20)) OUT_DT

                  ,CONVERT(DATETIME,SUBSTRING(LEFT(T2.INOUTTIME,8)+' ' + SUBSTRING(T2.INOUTTIME,9,2)+':' + SUBSTRING(T2.INOUTTIME,11,2)+':' + SUBSTRING(T2.INOUTTIME,13,2),1,20)) IN_DT

                  ,T1.INOUTGBNCD

                  ,T2.INOUTGBNCD INOUTGBNCD1

           FROM #DI_V2_BFGATE T1

           LEFT JOIN #DI_V2_BFGATE T2 ON(T2.IDNO = T1.IDNO

                                      AND T2.WORKDT = T1.WORKDT

                                      AND T2.RN = T1.RN + 1

                                      AND IsNumeric(T2.IDNO) = 1

                                      AND T2.INOUTGBNCD != T1.INOUTGBNCD)

           WHERE T1.INOUTGBNCD = 'OUT'   

           AND  T1.WORKDT >= '20170101' AND T1.WORKDT < '20170201'               

           AND  RIGHT(T1.INOUTTIME,6) >= '090000' AND RIGHT(T1.INOUTTIME,6) <= '110000'

           --OR   RIGHT(T1.INOUTTIME,6) >= '140000' AND RIGHT(T1.INOUTTIME,6) <= '150000')

       )T

       SELECT * FROM @WKT_TEMP

       DROP TABLE #DI_V2_BFGATE

小結

選擇對應的方式：

　1)使用表變量主要需要考慮的就是應用程序對內存的壓力，如果代碼的運行實例很多，就要特別注意內存變量對內存的消耗。我們對于較小的數據或者是通過計算出來的推薦使用表變量。如果數據的結果比較大，在代碼中用于臨時計算，在選取的時候沒有什么分組的聚合，就可以考慮使用表變量。

　2)一般對于大的數據結果，或者因為統(tǒng)計出來的數據為了便于更好的優(yōu)化，我們就推薦使用臨時表，同時還可以創(chuàng)建索引，由于臨時表是存放在Tempdb中，一般默認分配的空間很少，需要對tempdb進行調優(yōu)，增大其存儲的空間。