日期:2014-05-18 浏览次数:20491 次
create table A (A datetime,B CHAR,C INT) INSERT INTO A SELECT '20100830','b',3 SELECT dateadd(dd,b.number,A) A,B,1 C FROM a JOIN [master].dbo.spt_values b ON 1=1 WHERE b.[type] = 'P' AND b.number < a.C drop table a /* A B C ------------------------------------------------------ ---- ----------- 2010-08-30 00:00:00.000 b 1 2010-08-31 00:00:00.000 b 1 2010-09-01 00:00:00.000 b 1 (所影响的行数为 3 行) */
------解决方案--------------------
create table A (A datetime,B CHAR,C INT) INSERT INTO A SELECT '20100830','b',3 union SELECT '20100930','b',5 SELECT dateadd(dd,b.number,A) A,B,1 C FROM a JOIN [master].dbo.spt_values b ON 1=1 WHERE b.[type] = 'P' AND b.number < a.C drop table a /* A B C ------------------------------------------------------ ---- ----------- 2010-08-30 00:00:00.000 b 1 2010-08-31 00:00:00.000 b 1 2010-09-01 00:00:00.000 b 1 2010-09-30 00:00:00.000 b 1 2010-10-01 00:00:00.000 b 1 2010-10-02 00:00:00.000 b 1 2010-10-03 00:00:00.000 b 1 2010-10-04 00:00:00.000 b 1 (所影响的行数为 8 行) */
------解决方案--------------------
DECLARE @A TABLE (A datetime,B CHAR,C INT) INSERT INTO @A(A,B,C) SELECT '20100830','b',3 UNION ALL SELECT '20100830','d',2 UNION ALL SELECT '20100830','f',4 SELECT A,B,1,DATEADD(DD,b.number,A) as A1 FROM @A a JOIN [master].dbo.spt_values b ON 1=1 WHERE b.[type] = 'P' AND b.number < a.C