问SQL写法？该如何解决

问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'goselect max(convert(varchar(10),日期,120)) as 日期,convert(varchar(2),日期,108) 时间,isnull(sum(数字),0) 数字总和from tbwhere 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   302012-03-28 07   602012-03-29 05   60(3 行受影响)
[解决办法]
select substring(convert(char,日期,120),1,13),sum(数字) from tb group by substring(convert(char,日期,120),1,13)