本文介绍了填充日期时间列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!
问题描述
我想在存储过程中动态填充日期时间列.下面是我目前使用的查询,但会降低查询性能.
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.
这篇关于填充日期时间列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!