统计任意时段内各产品销量的总和.

Question

create table sales (productid int,saledate datetime,quantity int)
insert into sales values ('001','2005-9-1 08:00:00',10)
insert into sales values ('001','2005-9-1 09:00:00',12)
insert into sales values ('001','2005-9-1 13:00:00',15)
insert into sales values ('001','2005-9-1 14:00:00',20)
insert into sales values ('002','2005-9-1 07:00:00',10)
insert into sales values ('002','2005-9-1 10:00:00',20)
insert into sales values ('002','2005-9-1 15:00:00',30)
go
select productid,saledate,
quantity=sum(quantity)
from sales where saledate>'2005-9-1 08:00:00' and saledate<='2005-9-1 15:00:00'
group by productid,saledate order by productid,saledate
/*
productid   saledate                                               quantity    
----------- ------------------------------------------------------ ----------- 
1           2005-09-01 09:00:00.000                                12
1           2005-09-01 13:00:00.000                                15
1           2005-09-01 14:00:00.000                                20
2           2005-09-01 10:00:00.000                                20
2           2005-09-01 15:00:00.000                                30（所影响的行数为 5 行）
*/drop table sales

Answer 1

--测试表及数据
create table sales (productid varchar(20),saledate datetime,quantity int)
insert into sales values ('001','2005-9-1 08:00:00',10)
insert into sales values ('001','2005-9-1 09:00:00',12)
insert into sales values ('001','2005-9-1 13:00:00',15)
insert into sales values ('001','2005-9-1 14:00:00',20)
insert into sales values ('002','2005-9-1 07:00:00',10)
insert into sales values ('002','2005-9-1 10:00:00',20)
insert into sales values ('002','2005-9-1 15:00:00',30)
go--辅助函数
create function fnt_getdt(@dts datetime,@dte datetime,@hour int)
returns @re table(item_id int identity(1,1),dt datetime)
as
begin
insert into @re
values (@dts)
select @dts=dateadd(hour,@hour,@dts)
while @dts<=@dte
begin
insert into @re values(@dts)
select @dts=dateadd(hour,@hour,@dts)
end
return
end
go
--输入值
declare @dts datetime,@dte datetime,@hour int
select @dts='2005-9-1 08:00:00'
select @dte='2005-9-1 15:00:00'
select @hour=1
--处理语句
select a.productid,b.dt1,b.dt2,quantity=(select sum(quantity) from sales where productid=a.productid and saledate> b.dt1 and saledate<=b.dt2)
from (select distinct productid from sales) a,
(select dt1=dt,dt2=case when dateadd(hour,@hour,dt)>@dte then @dte else dateadd(hour,@hour,dt) end
 from fnt_getdt(@dts,@dte,@hour) a
) b/*
结果
001 2005-09-01 08:00:00.000 2005-09-01 09:00:00.000 12
001 2005-09-01 09:00:00.000 2005-09-01 10:00:00.000 NULL
001 2005-09-01 10:00:00.000 2005-09-01 11:00:00.000 NULL
001 2005-09-01 11:00:00.000 2005-09-01 12:00:00.000 NULL
001 2005-09-01 12:00:00.000 2005-09-01 13:00:00.000 15
001 2005-09-01 13:00:00.000 2005-09-01 14:00:00.000 20
001 2005-09-01 14:00:00.000 2005-09-01 15:00:00.000 NULL
001 2005-09-01 15:00:00.000 2005-09-01 15:00:00.000 NULL
002 2005-09-01 08:00:00.000 2005-09-01 09:00:00.000 NULL
002 2005-09-01 09:00:00.000 2005-09-01 10:00:00.000 20
002 2005-09-01 10:00:00.000 2005-09-01 11:00:00.000 NULL
002 2005-09-01 11:00:00.000 2005-09-01 12:00:00.000 NULL
002 2005-09-01 12:00:00.000 2005-09-01 13:00:00.000 NULL
002 2005-09-01 13:00:00.000 2005-09-01 14:00:00.000 NULL
002 2005-09-01 14:00:00.000 2005-09-01 15:00:00.000 30
002 2005-09-01 15:00:00.000 2005-09-01 15:00:00.000 NULL*/

Answer 2

数据量大（产品id多，时间跨度大）时。速度肯定会慢的。
因为按照报表需要产生连续的时间段，同时所有数据与每个产品id关联一下。
按照这个报表结果。暂时没有想到更好的方法。