MyException - 我的异常网
当前位置:我的异常网» Sql Server » 关于时间段的求和解决方案

关于时间段的求和解决方案

www.MyException.Cn  网友分享于:2013-01-05  浏览:17次
关于时间段的求和
求一天数据和,如下
icd                    tm                                     x
1300     2012-06-04 22:00:00.000 57.84                 50
1300     2012-06-04 22:05:00.000 57.84                 50
1300     2012-06-04 22:10:00.000 57.84                 50                  
1300     2012-06-04 22:15:00.000 57.84                 50
1311     2012-06-04 22:20:00.000 57.84                 50
1311     2012-06-04 22:25:00.000 57.84                 50
1311     2012-06-04 22:30:00.000 57.84                 50
1311     2012-06-04 22:35:00.000 57.84                 50


输出
icd              tm_day                                     sum_x
1300     2012-06-04                                  200
1311      2012-06-04                                  200

谢谢!

------解决方案--------------------
create table tb(icd int,tm datetime,y decimal(10,2),x int)
insert into tb select 1300,'2012-06-04 22:00:00.000',57.84,50
insert into tb select 1300,'2012-06-04 22:05:00.000',57.84,50
insert into tb select 1300,'2012-06-04 22:10:00.000',57.84,50                  
insert into tb select 1300,'2012-06-04 22:15:00.000',57.84,50
insert into tb select 1311,'2012-06-04 22:20:00.000',57.84,50
insert into tb select 1311,'2012-06-04 22:25:00.000',57.84,50
insert into tb select 1311,'2012-06-04 22:30:00.000',57.84,50
insert into tb select 1311,'2012-06-04 22:35:00.000',57.84,50
go
select icd,convert(varchar(10),tm,120) as tm_day,sum(x) sum_x
from tb
group by icd,convert(varchar(10),tm,120)
/*
icd         tm_day     sum_x
----------- ---------- -----------

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有