填充日期时间列
- 作者: 那晚越女说我?
- 来源: 51数据库
- 2023-02-07
问题描述
我想在存储过程中动态填充日期时间列.下面是我目前使用的查询,但会降低查询性能.
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.
推荐阅读
热点文章
检查拆分键盘
0
带有“上一个"的工具栏和“下一个"用于键盘输入AccessoryView
0
Activity 启动时显示软键盘
0
UIWebView 键盘 - 摆脱“上一个/下一个/完成"酒吧
0
在 iOS7 中边缘滑动时,使键盘与 UIView 同步动画
0
我的 iOS 应用程序中的键盘在 iPhone 6 上太高了.如何在 XCode 中调整键盘的分辨率?
0
android:inputType="textEmailAddress";- '@' 键和 '.com' 键?
0
禁用 iPhone 中键盘的方向
0
Android 2.3 模拟器上的印地语键盘问题
0
keyDown 没有被调用
0