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)
productid 不只下面列出的这两个,要统计的开始时间和结束时间可以指定，要按照某一时段统计的时间段也可以指定.
现在数据表 sales 中有在某个时间销售不同产品的记录,都是整点1小时这样子的,现在要统计任意小时内各产品销售数量的总和
比如3小时，6小时，12小时等等,
注意: 2005-9-1 08:00:00 10 这样的记录代表的不是08:00这个时间的记录，而是 2005-9-1 07:00:00-2005-9-1 08:00:00 之间的记录
也就是说统计某个时段数量的累加不是 between atime and btime 而是 > atime and <=btime
下面是统计 2005-9-1 08:00:00 到 2005-9-1 15:00:00 这段时间内1小时段销售产品数量的记录
这是1小时段的数据，要得到下面的结果
productid saledate quantity
001 2005-9-1 08:00:00-2005-9-1 09:00:00 12
001 2005-9-1 09:00:00-2005-9-1 10:00:00
001 2005-9-1 10:00:00-2005-9-1 11:00:00
001 2005-9-1 11:00:00-2005-9-1 12:00:00
001 2005-9-1 12:00:00-2005-9-1 13:00:00 15
001 2005-9-1 13:00:00-2005-9-1 14:00:00 20
001 2005-9-1 14:00:00-2005-9-1 15:00:00
002 2005-9-1 08:00:00-2005-9-1 09:00:00
002 2005-9-1 09:00:00-2005-9-1 10:00:00 20
002 2005-9-1 10:00:00-2005-9-1 11:00:00
002 2005-9-1 11:00:00-2005-9-1 12:00:00
002 2005-9-1 12:00:00-2005-9-1 13:00:00
002 2005-9-1 13:00:00-2005-9-1 14:00:00
002 2005-9-1 14:00:00-2005-9-1 15:00:00 30
这样也可以（如果得到上面这样的结果速度较慢的话，那么得到下面这样的结果也很好）
productid saledate quantity
001 2005-9-1 08:00:00-2005-9-1 09:00:00 12
001 2005-9-1 12:00:00-2005-9-1 13:00:00 15
001 2005-9-1 13:00:00-2005-9-1 14:00:00 20
002 2005-9-1 09:00:00-2005-9-1 10:00:00 20
002 2005-9-1 14:00:00-2005-9-1 15:00:00 30
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
下面是统计 2005-9-1 08:00:00 到 2005-9-1 15:00:00 这段时间内1小时段销售产品数量的记录
这是3小时段的数据,要得到下面的结果
productid saledate quantity
001 2005-9-1 08:00:00-2005-9-1 11:00:00 12
001 2005-9-1 11:00:00-2005-9-1 14:00:00 35
001 2005-9-1 14:00:00-2005-9-1 17:00:00
002 2005-9-1 08:00:00-2005-9-1 11:00:00 20
002 2005-9-1 11:00:00-2005-9-1 14:00:00
002 2005-9-1 14:00:00-2005-9-1 17:00:00 30
2 个解决方案
#1
--引用 wgsasd311(自强不息)
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
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
#2
请问楼上的解答，对我有什么帮助
#1
--引用 wgsasd311(自强不息)
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
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
#2
请问楼上的解答，对我有什么帮助