现有一张表tableA
create table tableA(
nid int identity(1,1)
FBillNo varchar(10),
FStadate smalldatetime,
FEndDate smalldatetime,
FQty int
)表中有如下记录:
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00001','2011-07-13','2011-07-15',100
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00001','2011-07-14','2011-07-17',100insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00002','2011-07-14','2011-07-16',100
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00002','2011-07-15','2011-07-17',100
现给一个查询时间段,如2011-07-13 至 2011-07-18 ,要求从tableA表中统计这个时间段在FStaDate,FEndDate范围内,按FBillNo分组每天的数量。即统计结果如下:FBillNo 日期 数量
00001 2011-07-13 100
00001 2011-07-14 200
00001 2011-07-15 200
00001 2011-07-16 100
00001 2011-07-17 100
00001 2011-07-18 000002 2011-07-13 0
00002 2011-07-14 100
00002 2011-07-15 200
00002 2011-07-16 200
00002 2011-07-17 100
00002 2011-07-18 0
请问这种统计结果的SQL语句要怎么写?最好是要能一条语句解决。
create table tableA(
nid int identity(1,1)
FBillNo varchar(10),
FStadate smalldatetime,
FEndDate smalldatetime,
FQty int
)表中有如下记录:
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00001','2011-07-13','2011-07-15',100
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00001','2011-07-14','2011-07-17',100insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00002','2011-07-14','2011-07-16',100
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00002','2011-07-15','2011-07-17',100
现给一个查询时间段,如2011-07-13 至 2011-07-18 ,要求从tableA表中统计这个时间段在FStaDate,FEndDate范围内,按FBillNo分组每天的数量。即统计结果如下:FBillNo 日期 数量
00001 2011-07-13 100
00001 2011-07-14 200
00001 2011-07-15 200
00001 2011-07-16 100
00001 2011-07-17 100
00001 2011-07-18 000002 2011-07-13 0
00002 2011-07-14 100
00002 2011-07-15 200
00002 2011-07-16 200
00002 2011-07-17 100
00002 2011-07-18 0
请问这种统计结果的SQL语句要怎么写?最好是要能一条语句解决。
insert into tableA(FBillNo,FStaDate,FEndDate,FQty) select '00001','2011-07-14','2011-07-17',100怎么出现的结果:
00001 2011-07-13 100
00001 2011-07-14 200
00001 2011-07-15 200
00001 2011-07-16 100
00001 2011-07-17 100
00001 2011-07-18 02011-07-14在两个时间段内都有,所以就是200?
declare @begin_date datetime;set @begin_date='2011-07-13'
declare @end_date datetime;set @end_date='2011-07-18'select b.FBillNo,
日期=convert(varchar(10),dateadd(dd,number,@begin_date),120),
数量=
sum(case when dateadd(dd,number,@begin_date) between c.FStadate and c.FEndDate then c.FQty else 0 end)
from master..spt_values a cross join(select distinct FBillNo from tablea) b
left join tablea c on c.FBillNo=b.FBillNo
where a.type='p' and dateadd(dd,a.number,@begin_date)<=@end_date
group by convert(varchar(10),dateadd(dd,number,@begin_date),120),b.FBillNo
order by FBillNo,日期
/*
FBillNo 日期 数量
---------- ---------- -----------
00001 2011-07-13 100
00001 2011-07-14 200
00001 2011-07-15 200
00001 2011-07-16 100
00001 2011-07-17 100
00001 2011-07-18 0
00002 2011-07-13 0
00002 2011-07-14 100
00002 2011-07-15 200
00002 2011-07-16 200
00002 2011-07-17 100
00002 2011-07-18 0
*/