create table tb(数字 int,时间 datetime
)insert into tb select 10, ' 2011-08-21 02:00:00.000'insert into tb select 10, ' 2011-08-21 02:10:00.000'insert into tb select 10, ' 2011-08-21 02:20:00.000'
insert into tb select 20, ' 2011-08-21 03:00:00.000'insert into tb select 20, ' 2011-08-21 03:10:00.000'insert into tb select 20, ' 2011-08-21 03:20:00.000'--我想得到这样格式的报表,该怎样写SQL日期 时间 数字2011-08-21 02 30
2011-08-21 03 60--select sum(数字)from tb where 时间 between '2011-08-21 02:00' and '2011-08-21 02:59'
drop table tb
)insert into tb select 10, ' 2011-08-21 02:00:00.000'insert into tb select 10, ' 2011-08-21 02:10:00.000'insert into tb select 10, ' 2011-08-21 02:20:00.000'
insert into tb select 20, ' 2011-08-21 03:00:00.000'insert into tb select 20, ' 2011-08-21 03:10:00.000'insert into tb select 20, ' 2011-08-21 03:20:00.000'--我想得到这样格式的报表,该怎样写SQL日期 时间 数字2011-08-21 02 30
2011-08-21 03 60--select sum(数字)from tb where 时间 between '2011-08-21 02:00' and '2011-08-21 02:59'
drop table tb
select
max(convert(varchar(10),时间,120)) as 日期,
convert(varchar(2),时间,108) 时间,
sum(数字) 数字
from tb
group by convert(varchar(2),时间,108)
/*
日期 时间 数字
---------- ---- -----------
2011-08-21 02 30
2011-08-21 03 60
*/