问题:
有下面数据
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 行受影响)
*/