循环不起作用SQL [英] Loop does not work SQL
本文介绍了循环不起作用SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的所有人,
我的SQL Script中有一个问题。我需要为以下条件插入数据。
i有一个itemmaster,它包含100个REcords,我还创建了年度日历,
我需要使用ITEMMASTER和日历表进行映射。
年内没有天数:365
ITEM中的记录数量Master:100
条件:
EAch ITEMCODE为每个日期添加(365)
怎么做?
Dear All,
I have one issue in my SQL Script.I need to Insert a data for the following Conditions.
i have a itemmaster ,it contains 100 REcords, i also Create Calendar for the year,
I need map with ITEMMASTER an Calendar Table.
No of days in Year :365
No of Records in ITEM Master :100
Condition :
EAch ITEMCODE add for Each Dates(365)
How to do this?
推荐答案
见这里:http://www.techonthenet.com/sql_server/loops/for_loop.php [ ^ ]
另一种选择是使用 CTE [ ^ ]。
有关详细信息,请参阅:使用公用表表达式 [ ^ ]
WITH common_table_expression(Transact-SQL) [ ^ ]
Another option is to use CTE[^].
For further information, please see: Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL)[^]
DECLARE @items TABLE(ItemMaster INT IDENTITY(1,1), ItemName VARCHAR(30))
INSERT INTO @items(ItemName)
VALUES ('A'), ('B'), ('C')
;WITH CTE AS
(
SELECT 1 As NoOfDay
UNION ALL
SELECT NoOfDay +1 AS NoOfDay
FROM CTE
WHERE NoOfDay < 366
)
SELECT t2.ItemMaster, CONVERT(DATE, DATEADD(DD, t1.NoOfDay, GETDATE())) AS MyDate
FROM CTE AS t1, @items AS t2
ORDER BY t2.ItemMaster, t1.NoOfDay
OPTION (MAXRECURSION 0)
结果:
Result:
ItemMaster MyDate
1 2015-10-06
1 2015-10-07
1 2015-10-08
1 2015-10-09
1 2015-10-10
...
2 2015-10-06
2 2015-10-07
2 2015-10-08
2 2015-10-09
2 2015-10-10
...
3 2015-10-06
3 2015-10-07
3 2015-10-08
3 2015-10-09
3 2015-10-10
这篇关于循环不起作用SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文