填充日期时间列

问题描述

我想在存储过程中动态填充日期时间列.下面是我目前使用的查询，但会降低查询性能.

I want to populate a datetime column on the fly within a stored procedure. below is the query that I currently have that does same but slows down query performance.

CREATE TABLE #TaxVal
(
    ID         INT
    , PaidDate DATETIME
    , CustID   INT
    , CompID   INT 
)

INSERT INTO #TaxVal(ID, PaidDate, CustID, CompID)
VALUES(01, '20150201',12, 100)
    , (03,'20150301', 18,101)
    , (10,'20150401',19,22)
    , (17,'20150401',02,11)
    , (11,'20150411',18,201)
    , (78,'20150421',18,299)
    , (133,'20150407',18,101)

--  SELECT * FROM #TaxVal

DECLARE @StartDate DATETIME = '20150101'
    , @EndDate     DATETIME = '20150501'

DECLARE @Tab TABLE 
(
    CompID    INT
    , DateField DATETIME
)

DECLARE @T INT
SET @T = 0
WHILE @EndDate >= @StartDate + @T 
BEGIN
    INSERT INTO @Tab 
    SELECT CompID
         , @StartDate + @T AS DateField
    FROM #TaxVal
    WHERE CustID = 18
        AND CompID = 101
    ORDER BY DateField DESC

    SET @T = @T + 1
END

SELECT DISTINCT * FROM @Tab 

DROP TABLE #TaxVal

编写此查询以获得更好性能的最佳方法是什么?

Which is the best way to write this query for better performance?

推荐答案

改变这个:

DECLARE @T INT
SET @T = 0
WHILE @EndDate >= @StartDate + @T 
BEGIN
    INSERT INTO @Tab 
SELECT CompID
     , @StartDate + @T AS DateField
FROM #TaxVal
WHERE CustID = 18
    AND CompID = 101
ORDER BY DateField DESC

SET @T = @T + 1
END

为此:

;with cte as(
select cast('20150101' as date) as d
union all
select dateadd(dd, 1, d) as d from cte where d < '20150501'
)
INSERT INTO @Tab
SELECT CompID, d
FROM #TaxVal 
cross join cte
WHERE CustID = 18 AND CompID = 101
Option(maxrecursion 0)

这是获取范围内所有日期的递归公用表表达式.然后你做一个 cross join 并插入.请注意，插入时设置顺序是没有意义的.

Here is recursive common table expression to get all dates in range. Then you do a cross join and insert. Notice that there is no sense to order set while inserting.