区间统计应用实例

问题：

有下面数据
a b c
271108 1.00 0.01
271109 1.00 0.01
271110 1.00 0.01
271111 1.00 0.01
271112 1.00 1.00
271113 2.00 0.01
271114 1.00 0.01
271115 1.00 0.01

得到的结果
a sum（b） sum（c）
271108 4.00 0.04
271112 1.00 1.00
271113 2.00 0.01
271114 2.00 0.02

也就是分别比较 b值和c，如果连续的b c值相同则求和，并得到第一个a值，不要游标方式

-----------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-04-21 20:56:37

-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-----------------------------------------------------------------------

--> 生成测试数据表:tb

if not object_id('[tb]') is null

drop table [tb]

go

create table [tb]([a] int,[b] decimal(18,2),[c] decimal(9,2))

insert [tb]

select 271108,1.00,'0.01' union all

select 271109,1.00,'0.01' union all

select 271110,1.00,'0.01' union all

select 271111,1.00,'0.01' union all

select 271112,1.00,'1.00' union all

select 271113,2.00,'0.01' union all

select 271114,1.00,'0.01' union all

select 271115,1.00,'0.01'

go

--select * from [tb]

-->SQL查询如下:

--1.直接查询法：

select min(a) a,sum(b) b,sum(c) c

from (

select a,b,c,

d=a-(select count(1) from tb

where a < a.a and b = a.b and c = a.c)

from tb a

) t

group by d

--2.辅助字段查询法

alter table tb add d int

go

declare @j int,@b dec(9,2),@c dec(9,2)

update tb set

d = @j,

@j = case when @b=b and @c=c then @j

else isnull(@j,0)+1

end,

@b = b,

@c = c

select min(a) a,sum(b) b,sum(c) c

from tb

group by d

order by a

/*

a b c

----------- ----------- ----------------

271108 4.00 0.04

271112 1.00 1.00

271113 2.00 0.01

271114 2.00 0.02

(4 行受影响)

*/