select id,saledate=convert(char(10),dateadd(hour,-9,saledate),120),quantity=sum(quantity) from sales group by id,saledate=convert(char(10),dateadd(hour,-9,saledate),120)
--有点小错,改一下:select id,saledate=convert(char(10),dateadd(hour,-9,saledate),120),quantity=sum(quantity) from sales group by id,convert(char(10),dateadd(hour,-9,saledate),120) order by id,saledate
create table sales(id int,saledate datetime,quantity int)insert into sales values(1,'2005-5-8 05:00:00',10)----这个是id为1的'2005-5-7'的销量 insert into sales values(1,'2005-5-8 06:00:00',10)--同上 insert into sales values(1,'2005-5-8 07:00:00',10)--同上 insert into sales values(1,'2005-5-8 08:00:00',10)----这个是id为1的'2005-5-7'的销量insert into sales values(1,'2005-5-8 09:00:00',10)----这个是id为1的'2005-5-8'的销量 insert into sales values(1,'2005-5-8 10:00:00',10)--同上 insert into sales values(1,'2005-5-8 11:00:00',10)--同上 insert into sales values(1,'2005-5-8 12:00:00',10)--同上 insert into sales values(1,'2005-5-8 13:00:00',10)--同上 insert into sales values(1,'2005-5-8 14:00:00',10)--同上 insert into sales values(1,'2005-5-8 15:00:00',10)--同上 insert into sales values(1,'2005-5-8 16:00:00',10)--同上 insert into sales values(1,'2005-5-8 17:00:00',10)--同上 insert into sales values(1,'2005-5-8 18:00:00',10)--同上 insert into sales values(1,'2005-5-8 19:00:00',10)--同上 insert into sales values(1,'2005-5-8 20:00:00',10)--同上 insert into sales values(1,'2005-5-8 21:00:00',10)--同上 insert into sales values(1,'2005-5-8 22:00:00',10)--同上 insert into sales values(1,'2005-5-8 23:00:00',10)--同上insert into sales values(1,'2005-5-9 00:00:00',10)--同上 insert into sales values(1,'2005-5-9 01:00:00',10)--同上 insert into sales values(1,'2005-5-9 02:00:00',10)--同上 insert into sales values(1,'2005-5-9 03:00:00',10)--同上 insert into sales values(1,'2005-5-9 04:00:00',10)--同上 insert into sales values(1,'2005-5-9 05:00:00',10)--同上 insert into sales values(1,'2005-5-9 06:00:00',10)--同上 insert into sales values(1,'2005-5-9 07:00:00',10)--同上 insert into sales values(1,'2005-5-9 08:00:00',10)----这个是id为1的'2005-5-8'的销量insert into sales values(1,'2005-5-9 09:00:00',10)----这个是id为1的'2005-5-9'的销量 insert into sales values(1,'2005-5-9 10:00:00',10)--同上 insert into sales values(1,'2005-5-9 11:00:00',10)--同上 insert into sales values(1,'2005-5-9 12:00:00',10)--同上 insert into sales values(1,'2005-5-9 13:00:00',10)--同上 insert into sales values(1,'2005-5-9 14:00:00',10)--同上 insert into sales values(1,'2005-5-9 15:00:00',10)--同上 insert into sales values(1,'2005-5-9 16:00:00',10)--同上 insert into sales values(1,'2005-5-9 17:00:00',10)--同上 insert into sales values(1,'2005-5-9 18:00:00',10)--同上 insert into sales values(1,'2005-5-9 19:00:00',10)--同上 insert into sales values(1,'2005-5-9 20:00:00',10)--同上 insert into sales values(1,'2005-5-9 21:00:00',10)--同上 insert into sales values(1,'2005-5-9 22:00:00',10)--同上 insert into sales values(1,'2005-5-9 23:00:00',10)----这个是id为1的'2005-5-9'的销量 insert into sales values(2,'2005-5-8 00:00:00',10)----这个是id为2的'2005-5-7'的销量 insert into sales values(2,'2005-5-8 01:00:00',10)--同上 insert into sales values(2,'2005-5-8 02:00:00',10)--同上 insert into sales values(2,'2005-5-8 03:00:00',10)--同上 insert into sales values(2,'2005-5-8 04:00:00',10)--同上 insert into sales values(2,'2005-5-8 05:00:00',10)--同上 insert into sales values(2,'2005-5-8 06:00:00',10)--同上 insert into sales values(2,'2005-5-8 07:00:00',10)--同上 insert into sales values(2,'2005-5-8 08:00:00',10)----这个是id为2的'2005-5-7'的销量insert into sales values(2,'2005-5-8 09:00:00',10)----这个是id为2的'2005-5-8'的销量 insert into sales values(2,'2005-5-8 10:00:00',10)--同上 insert into sales values(2,'2005-5-8 11:00:00',10)--同上 insert into sales values(2,'2005-5-8 12:00:00',10)--同上 insert into sales values(2,'2005-5-8 13:00:00',10)--同上 insert into sales values(2,'2005-5-8 14:00:00',10)--同上 insert into sales values(2,'2005-5-8 15:00:00',10)--同上 insert into sales values(2,'2005-5-8 16:00:00',10)--同上 insert into sales values(2,'2005-5-8 17:00:00',10)--同上 insert into sales values(2,'2005-5-8 18:00:00',10)--同上 insert into sales values(2,'2005-5-8 19:00:00',10)--同上 insert into sales values(2,'2005-5-8 20:00:00',10)--同上 insert into sales values(2,'2005-5-8 21:00:00',10)--同上 insert into sales values(2,'2005-5-8 22:00:00',10)--同上 insert into sales values(2,'2005-5-8 23:00:00',10)--同上insert into sales values(2,'2005-5-9 00:00:00',10)--同上 insert into sales values(2,'2005-5-9 01:00:00',10)--同上 insert into sales values(2,'2005-5-9 02:00:00',10)--同上 insert into sales values(2,'2005-5-9 03:00:00',10)--同上 insert into sales values(2,'2005-5-9 04:00:00',10)--同上 insert into sales values(2,'2005-5-9 05:00:00',10)--同上 insert into sales values(2,'2005-5-9 06:00:00',10)--同上 insert into sales values(2,'2005-5-9 07:00:00',10)--同上 insert into sales values(2,'2005-5-9 08:00:00',10)----这个是id为2的'2005-5-8'的销量insert into sales values(2,'2005-5-9 09:00:00',10)----这个是id为2的'2005-5-9'的销量 insert into sales values(2,'2005-5-9 10:00:00',10)--同上 insert into sales values(2,'2005-5-9 11:00:00',10)--同上 insert into sales values(2,'2005-5-9 12:00:00',10)--同上 insert into sales values(2,'2005-5-9 13:00:00',10)--同上 insert into sales values(2,'2005-5-9 14:00:00',10)--同上 insert into sales values(2,'2005-5-9 15:00:00',10)--同上 insert into sales values(2,'2005-5-9 16:00:00',10)--同上 insert into sales values(2,'2005-5-9 17:00:00',10)--同上 insert into sales values(2,'2005-5-9 18:00:00',10)--同上 insert into sales values(2,'2005-5-9 19:00:00',10)--同上 insert into sales values(2,'2005-5-9 20:00:00',10)--同上 insert into sales values(2,'2005-5-9 21:00:00',10)--同上 insert into sales values(2,'2005-5-9 22:00:00',10)--同上 insert into sales values(2,'2005-5-9 23:00:00',10)----这个是id为2的'2005-5-9'的销量 go--统计 select id,saledate=convert(char(10),dateadd(hour,-9,saledate),120),quantity=sum(quantity) from sales group by id,convert(char(10),dateadd(hour,-9,saledate),120) order by id,saledate go--删除测试 drop table sales/*--结果id saledate quantity ----------- ---------- ----------- 1 2005-05-07 40 1 2005-05-08 240 1 2005-05-09 150 2 2005-05-07 90 2 2005-05-08 240 2 2005-05-09 150(所影响的行数为 6 行) --*/
from sales
group by id,convert(char(10),dateadd(hour,-9,saledate),120)
order by id,saledate
insert into sales values(1,'2005-5-8 06:00:00',10)--同上
insert into sales values(1,'2005-5-8 07:00:00',10)--同上
insert into sales values(1,'2005-5-8 08:00:00',10)----这个是id为1的'2005-5-7'的销量insert into sales values(1,'2005-5-8 09:00:00',10)----这个是id为1的'2005-5-8'的销量
insert into sales values(1,'2005-5-8 10:00:00',10)--同上
insert into sales values(1,'2005-5-8 11:00:00',10)--同上
insert into sales values(1,'2005-5-8 12:00:00',10)--同上
insert into sales values(1,'2005-5-8 13:00:00',10)--同上
insert into sales values(1,'2005-5-8 14:00:00',10)--同上
insert into sales values(1,'2005-5-8 15:00:00',10)--同上
insert into sales values(1,'2005-5-8 16:00:00',10)--同上
insert into sales values(1,'2005-5-8 17:00:00',10)--同上
insert into sales values(1,'2005-5-8 18:00:00',10)--同上
insert into sales values(1,'2005-5-8 19:00:00',10)--同上
insert into sales values(1,'2005-5-8 20:00:00',10)--同上
insert into sales values(1,'2005-5-8 21:00:00',10)--同上
insert into sales values(1,'2005-5-8 22:00:00',10)--同上
insert into sales values(1,'2005-5-8 23:00:00',10)--同上insert into sales values(1,'2005-5-9 00:00:00',10)--同上
insert into sales values(1,'2005-5-9 01:00:00',10)--同上
insert into sales values(1,'2005-5-9 02:00:00',10)--同上
insert into sales values(1,'2005-5-9 03:00:00',10)--同上
insert into sales values(1,'2005-5-9 04:00:00',10)--同上
insert into sales values(1,'2005-5-9 05:00:00',10)--同上
insert into sales values(1,'2005-5-9 06:00:00',10)--同上
insert into sales values(1,'2005-5-9 07:00:00',10)--同上
insert into sales values(1,'2005-5-9 08:00:00',10)----这个是id为1的'2005-5-8'的销量insert into sales values(1,'2005-5-9 09:00:00',10)----这个是id为1的'2005-5-9'的销量
insert into sales values(1,'2005-5-9 10:00:00',10)--同上
insert into sales values(1,'2005-5-9 11:00:00',10)--同上
insert into sales values(1,'2005-5-9 12:00:00',10)--同上
insert into sales values(1,'2005-5-9 13:00:00',10)--同上
insert into sales values(1,'2005-5-9 14:00:00',10)--同上
insert into sales values(1,'2005-5-9 15:00:00',10)--同上
insert into sales values(1,'2005-5-9 16:00:00',10)--同上
insert into sales values(1,'2005-5-9 17:00:00',10)--同上
insert into sales values(1,'2005-5-9 18:00:00',10)--同上
insert into sales values(1,'2005-5-9 19:00:00',10)--同上
insert into sales values(1,'2005-5-9 20:00:00',10)--同上
insert into sales values(1,'2005-5-9 21:00:00',10)--同上
insert into sales values(1,'2005-5-9 22:00:00',10)--同上
insert into sales values(1,'2005-5-9 23:00:00',10)----这个是id为1的'2005-5-9'的销量
insert into sales values(2,'2005-5-8 00:00:00',10)----这个是id为2的'2005-5-7'的销量
insert into sales values(2,'2005-5-8 01:00:00',10)--同上
insert into sales values(2,'2005-5-8 02:00:00',10)--同上
insert into sales values(2,'2005-5-8 03:00:00',10)--同上
insert into sales values(2,'2005-5-8 04:00:00',10)--同上
insert into sales values(2,'2005-5-8 05:00:00',10)--同上
insert into sales values(2,'2005-5-8 06:00:00',10)--同上
insert into sales values(2,'2005-5-8 07:00:00',10)--同上
insert into sales values(2,'2005-5-8 08:00:00',10)----这个是id为2的'2005-5-7'的销量insert into sales values(2,'2005-5-8 09:00:00',10)----这个是id为2的'2005-5-8'的销量
insert into sales values(2,'2005-5-8 10:00:00',10)--同上
insert into sales values(2,'2005-5-8 11:00:00',10)--同上
insert into sales values(2,'2005-5-8 12:00:00',10)--同上
insert into sales values(2,'2005-5-8 13:00:00',10)--同上
insert into sales values(2,'2005-5-8 14:00:00',10)--同上
insert into sales values(2,'2005-5-8 15:00:00',10)--同上
insert into sales values(2,'2005-5-8 16:00:00',10)--同上
insert into sales values(2,'2005-5-8 17:00:00',10)--同上
insert into sales values(2,'2005-5-8 18:00:00',10)--同上
insert into sales values(2,'2005-5-8 19:00:00',10)--同上
insert into sales values(2,'2005-5-8 20:00:00',10)--同上
insert into sales values(2,'2005-5-8 21:00:00',10)--同上
insert into sales values(2,'2005-5-8 22:00:00',10)--同上
insert into sales values(2,'2005-5-8 23:00:00',10)--同上insert into sales values(2,'2005-5-9 00:00:00',10)--同上
insert into sales values(2,'2005-5-9 01:00:00',10)--同上
insert into sales values(2,'2005-5-9 02:00:00',10)--同上
insert into sales values(2,'2005-5-9 03:00:00',10)--同上
insert into sales values(2,'2005-5-9 04:00:00',10)--同上
insert into sales values(2,'2005-5-9 05:00:00',10)--同上
insert into sales values(2,'2005-5-9 06:00:00',10)--同上
insert into sales values(2,'2005-5-9 07:00:00',10)--同上
insert into sales values(2,'2005-5-9 08:00:00',10)----这个是id为2的'2005-5-8'的销量insert into sales values(2,'2005-5-9 09:00:00',10)----这个是id为2的'2005-5-9'的销量
insert into sales values(2,'2005-5-9 10:00:00',10)--同上
insert into sales values(2,'2005-5-9 11:00:00',10)--同上
insert into sales values(2,'2005-5-9 12:00:00',10)--同上
insert into sales values(2,'2005-5-9 13:00:00',10)--同上
insert into sales values(2,'2005-5-9 14:00:00',10)--同上
insert into sales values(2,'2005-5-9 15:00:00',10)--同上
insert into sales values(2,'2005-5-9 16:00:00',10)--同上
insert into sales values(2,'2005-5-9 17:00:00',10)--同上
insert into sales values(2,'2005-5-9 18:00:00',10)--同上
insert into sales values(2,'2005-5-9 19:00:00',10)--同上
insert into sales values(2,'2005-5-9 20:00:00',10)--同上
insert into sales values(2,'2005-5-9 21:00:00',10)--同上
insert into sales values(2,'2005-5-9 22:00:00',10)--同上
insert into sales values(2,'2005-5-9 23:00:00',10)----这个是id为2的'2005-5-9'的销量
go--统计
select id,saledate=convert(char(10),dateadd(hour,-9,saledate),120),quantity=sum(quantity)
from sales
group by id,convert(char(10),dateadd(hour,-9,saledate),120)
order by id,saledate
go--删除测试
drop table sales/*--结果id saledate quantity
----------- ---------- -----------
1 2005-05-07 40
1 2005-05-08 240
1 2005-05-09 150
2 2005-05-07 90
2 2005-05-08 240
2 2005-05-09 150(所影响的行数为 6 行)
--*/