我现在有个问题:
比如: 一张表中结构如下:
ID 日期 数量
1 2010.3.1 2
2 2010.3.2 2
现在要通过SQL得到如下表:
ID 日期 数量(新)
1 2010.3.1 1
1 2010.3.2 1
2 2010.3.2 1
2 2010.3.3 1
请问这个SQL语句如何编写.
比如: 一张表中结构如下:
ID 日期 数量
1 2010.3.1 2
2 2010.3.2 2
现在要通过SQL得到如下表:
ID 日期 数量(新)
1 2010.3.1 1
1 2010.3.2 1
2 2010.3.2 1
2 2010.3.3 1
请问这个SQL语句如何编写.
--this ?
create table T(id int, [date] datetime, qty int)
insert into T select 1,'2010-3-1',2
insert into T select 2,'2010-3-2',2
insert into T select 3,'2010-3-3',5
GOselect distinct T.id,dateadd(day,number,[date]) as [date],1 as qty
from T
left join master..spt_values
on number between 0 and qty-1GO
/*
1 2010-03-01 00:00:00.000 1
1 2010-03-02 00:00:00.000 1
2 2010-03-02 00:00:00.000 1
2 2010-03-03 00:00:00.000 1
3 2010-03-03 00:00:00.000 1
3 2010-03-04 00:00:00.000 1
3 2010-03-05 00:00:00.000 1
3 2010-03-06 00:00:00.000 1
3 2010-03-07 00:00:00.000 1*/
drop table T
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-12 11:38:24
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] DATETIME,[c] INT)
INSERT [tb]
SELECT 1,'2010.3.1',2 UNION ALL
SELECT 2,'2010.3.2',2
GO
--SELECT * FROM [tb]-->SQL查询如下:
;with t as
(
select A,convert(varchar(10),B,102) b,1 C,lvl=1 from tb
union all
select A.a,convert(varchar(10),DATEADD(DD,lvl-1,B.b),102),1,lvl+1
from tb a join t b on lvl+1<=a.c
)
select distinct A,B,C from t order by 1,2
/*
A B C
----------- ---------- -----------
1 2010.03.01 1
1 2010.03.02 1
2 2010.03.01 1
2 2010.03.02 1(4 行受影响)
*/