问SQL写法？


create table tb

(

数字 int,

日期 datetime
)

insert into tb select 10, ' 2011-08-21 06:00:00.000'

insert into tb select 10, ' 2011-08-21 06:10:00.000'

insert into tb select 10, ' 2011-08-21 06:20:00.000'


insert into tb select 20, ' 2011-08-21 07:00:00.000'

insert into tb select 20, ' 2011-08-21 07:10:00.000'

insert into tb select 20, ' 2011-08-21 07:20:00.000'

---------------------------------------------------略

insert into tb select 30, ' 2011-08-22 05:40:00.000'

insert into tb select 30, ' 2011-08-22 05:50:00.000'

insert into tb select 30, ' 2011-08-22 06:00:00.000'

/*
从当前日期6点开始，到第二天6点结束，总共24小时，要统计每个小时的数字总和
我写了SQL，但是结果不对

结果如下：

日期 时间 数字总和
2011-08-21 06 30  
2011-08-21 07 60 
.........................................................略
2011-08-22 06 90  
*/


select max(convert(varchar(10),日期,120)) as 日期,
convert(varchar(2),日期,108) 时间,isnull(sum(数字),0) 数字总和
from tb
group by convert(varchar(2),日期,108)
order by 日期

drop table tb

------解决方案--------------------

SQL code

create table tb
(
数字 int,
日期 datetime
)
insert into tb select 10, ' 2012-03-28 06:00:00.000'
insert into tb select 10, ' 2012-03-28 06:10:00.000'
insert into tb select 10, ' 2012-03-28 06:20:00.000'
insert into tb select 20, ' 2012-03-28 07:00:00.000'
insert into tb select 20, ' 2012-03-28 07:10:00.000'
insert into tb select 20, ' 2012-03-28 07:20:00.000'
insert into tb select 30, ' 2012-03-29 05:40:00.000'
insert into tb select 30, ' 2012-03-29 05:50:00.000'
insert into tb select 30, ' 2012-03-29 06:00:00.000'
go

select max(convert(varchar(10),日期,120)) as 日期,
convert(varchar(2),日期,108) 时间,isnull(sum(数字),0) 数字总和
from tb
where convert(varchar(10),dateadd(hh,-6,日期),120) = convert(varchar(10),getdate(),120)
group by convert(varchar(2),日期,108)
order by 日期

drop table tb

/**********************

日期         时间   数字总和
---------- ---- -----------
2012-03-28 06   30
2012-03-28 07   60
2012-03-29 05   60

(3 行受影响)

------解决方案--------------------

select substring(convert(char,日期,120),1,13),sum(数字) from tb group by substring(convert(char,日期,120),1,13)