在次询问增加行数的数据抽取(增加一列合法自增的日期列)
大概是接着这个帖子发的
http://topic.csdn.net/u/20100925/15/8f300393-cb4d-4b3d-a784-7051e932577a.html
源（列A是日期格式的，列C只有正整数，一般是x - xx那样子吧，最大可能上百）：
列：A B C
值：20100830 b 3
抽取后的目标：
列：A B C
值：20100830 b 1
20100831 b 1
20100901 b 1
跟前的不同就是要有一列是会合法自增的日期列
无论是sql语句还是etl过程都是可以的，谢谢
[解决办法]
- SQL code
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 CFROM a JOIN [master].dbo.spt_values b ON 1=1WHERE b.[type] = 'P' AND b.number < a.Cdrop table a/*A B C ------------------------------------------------------ ---- ----------- 2010-08-30 00:00:00.000 b 12010-08-31 00:00:00.000 b 12010-09-01 00:00:00.000 b 1（所影响的行数为 3 行）*/
[解决办法]
- SQL code
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 CFROM a JOIN [master].dbo.spt_values b ON 1=1WHERE b.[type] = 'P' AND b.number < a.Cdrop table a/*A B C ------------------------------------------------------ ---- ----------- 2010-08-30 00:00:00.000 b 12010-08-31 00:00:00.000 b 12010-09-01 00:00:00.000 b 12010-09-30 00:00:00.000 b 12010-10-01 00:00:00.000 b 12010-10-02 00:00:00.000 b 12010-10-03 00:00:00.000 b 12010-10-04 00:00:00.000 b 1（所影响的行数为 8 行）*/
[解决办法]
- SQL code
DECLARE @A TABLE (A datetime,B CHAR,C INT)INSERT INTO @A(A,B,C)SELECT '20100830','b',3 UNION ALLSELECT '20100830','d',2 UNION ALLSELECT '20100830','f',4SELECT A,B,1,DATEADD(DD,b.number,A) as A1FROM @A a JOIN [master].dbo.spt_values b ON 1=1WHERE b.[type] = 'P' AND b.number < a.C