分组获取最小时间行另外一列的和
日期：2014-05-17 浏览次数：20683 次
求助：分组获取最小时间行另外一列的和
如下面脚本，根据a分组，获取最小的时间列，然后求B列的和
------解决方案--------------------
如下面脚本，根据a分组，获取最小的时间列，然后求B列的和
create table #tb(a ID,dt datetime,b int,c int)
insert into #tb
select 1,N'2012-12-12', 1,2 union all
select 2,N'2012-12-12', 4,0 union all
select 3,N'2012-12-12', 8,3 union all
select 2,N'2012-12-12', 5,5 union all
select 1,N'2012-12-13', 14,13 union all
select 5,N'2012-12-13', 14,18 union all
select 3,N'2012-12-13', 21,33 union all
select 4,N'2012-12-13', 11,53 union all
select 5,N'2012-12-13', 11,25 union all
select 6,N'2012-12-13', 22,33 union all
select 3,N'2012-12-14', 27,33 union all
select 4,N'2012-12-14', 25,13 union all
select 8,N'2012-12-14', 32,53 union all
select 3,N'2012-12-14', 35,93
------解决方案--------------------
create table #tb(a int,dt datetime,b int,c int)
insert into #tb
select 1,N'2012-12-12', 1,2 union all
select 2,N'2012-12-12', 4,0 union all
select 3,N'2012-12-12', 8,3 union all
select 2,N'2012-12-12', 5,5 union all
select 1,N'2012-12-13', 14,13 union all
select 5,N'2012-12-13', 14,18 union all
select 3,N'2012-12-13', 21,33 union all
select 4,N'2012-12-13', 11,53 union all
select 5,N'2012-12-13', 11,25 union all
select 6,N'2012-12-13', 22,33 union all
select 3,N'2012-12-14', 27,33 union all
select 4,N'2012-12-14', 25,13 union all
select 8,N'2012-12-14', 32,53 union all
select 3,N'2012-12-14', 35,93
select
a,
dt,
sum(b) as b
from
#tb a
where
a.dt=(select min(dt) from #tb where a.a=a)
group by
a,dt
/*
a dt b
1 2012-12-12 00:00:00.000 1
2 2012-12-12 00:00:00.000 9
3 2012-12-12 00:00:00.000 8
4 2012-12-13 00:00:00.000 11
5 2012-12-13 00:00:00.000 25
6 2012-12-13 00:00:00.000 22
8 2012-12-14 00:00:00.000 32
*/
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
