按日期统计数据解决方案

按日期统计数据
a b
2010-06-27 20:04:00 -125.00
2010-06-27 20:12:00 3000.00
2010-06-27 20:33:00 450.00
2010-06-28 19:32:00 -117.00
2010-06-28 20:01:00 3000.00
2010-06-28 21:37:00 450.00
2010-06-29 11:33:00 -367.00
2010-06-29 21:37:00 -212.00
2010-06-30 12:08:00 -590.00
2010-06-30 12:49:00 -386.00

怎么按每天统计b列的值

[解决办法]
select convert(varchar(10),a,120) a , sum(b) b from tb group by convert(varchar(10),a,120)
[解决办法]
select a,sum(b) b
from
(
select convert(varchar(10),a,120) as a,b
) C
group by a
[解决办法]

SQL code

create table tb(a datetime,b decimal(18,2))insert into tb values('2010-06-27 20:04:00', -125.00)insert into tb values('2010-06-27 20:12:00', 3000.00)insert into tb values('2010-06-27 20:33:00', 450.00)insert into tb values('2010-06-28 19:32:00', -117.00)insert into tb values('2010-06-28 20:01:00', 3000.00)insert into tb values('2010-06-28 21:37:00', 450.00)insert into tb values('2010-06-29 11:33:00', -367.00)insert into tb values('2010-06-29 21:37:00', -212.00)insert into tb values('2010-06-30 12:08:00', -590.00)insert into tb values('2010-06-30 12:49:00', -386.00)goselect convert(varchar(10),a,120) a , sum(b) b from tb group by convert(varchar(10),a,120)drop table tb/*a          b                                        ---------- ---------------------------------------- 2010-06-27 3325.002010-06-28 3333.002010-06-29 -579.002010-06-30 -976.00（所影响的行数为 4 行）*/
[解决办法]
--生成测试数据：
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
a datetime,
b int
)
go
insert tbl
select '2010-06-27 20:04:00',-125.00 union all
select '2010-06-27 20:12:00',3000.00union all
select '2010-06-27 20:33:00',450.00union all
select '2010-06-28 19:32:00',-117.00union all
select '2010-06-28 20:01:00',3000.00union all
select '2010-06-28 21:37:00',450.00union all
select '2010-06-29 11:33:00',-367.00union all
select '2010-06-29 21:37:00',-212.00union all
select '2010-06-30 12:08:00',-590.00union all
select '2010-06-30 12:49:00',-386.00

--怎么按每天统计b列的值

select CONVERT(varchar(10),a,120) as a,
SUM(b) as b from tbl  
group by CONVERT(varchar(10),a,120)
/*
ab
2010-06-273325
2010-06-283333
2010-06-29-579
2010-06-30-976
*/