一道统计题目

[b哈利路亚d]

test表结构以及数据情况如下：
type bill free
A 1 50
A 2 100
A 3 150
A 4 200
A 9 500
B 12 200
C 1 50
C 3 150
C 4 200
.......
需要统计成如下：
type start end sum(free)
A 1 4 500
A 9 9 500
B 12 12 200
C 1 1 50
C 3 4 350

将相同type的连续bill的free累加起来,用一条ｓｑｌ语句实现．
不能使用游标

[b哈利路亚d]

咋就没有高手回复呢？

[playwarcraft]

--就直接點寫了，可以改改
create table T(type char(01), bill int, [free] int)
insert into T select 'A',1,50
insert into T select 'A',2,100
insert into T select 'A',3,150
insert into T select 'A',4,200
insert into T select 'A',6,88888
insert into T select 'A',9,500
insert into T select 'B',12,200
insert into T select 'C',1,50
insert into T select 'C',3,150
insert into T select 'C',4,200
GO

select  type, min_bill as [start], max_bill as [end],
   [sum(free)]=(select sum([free])  from  T where  type=X.type and bill>=min_bill  and bill <=max_bill)
from
(
select type, bill as min_bill,(select min(bill) from T b where b.type=a.type and b.bill>=a.bill
                            and not exists(select 1 from T where type=b.type and bill=b.bill+1)) as max_bill
from  T  a
where  not exists(select 1 from T where type=a.type and bill=a.bill-1)
) X

/* --result:
type start       end         sum(free)   
---- ----------- ----------- ----------- 
A    1           4           500
A    6           6           88888
A    9           9           500
B    12          12          200
C    1           1           50
C    3           4           350
*/
GO
drop table T

[昵称被占用了]

用一个语句是很累的，不过我写出来了

declare @test table (
type    varchar(2),
bill  int,
free int
)
insert @test select
'A',        1 ,     50 
union all select
'A',        2 ,   100 
union all select
'A',        3 ,   150 
union all select
'A',        4 ,   200 
union all select
'A',        9 ,   500 
union all select
'B',        12,    200 
union all select
'C',        1 ,     50 
union all select
'C',        3 ,   150 
union all select
'C',        4 ,   200 

select a.type,a.bill as start,b.bill as [end],sum(c.free) as [sum(free)]
from  (
select * from @test t where not exists (select 1 from @test where type=t.type and bill=t.Bill-1)
) as a,(
select * from @test t where not exists (select 1 from @test where type=t.type and bill=t.Bill+1)
) as b,@Test c
where a.type=b.type 
and a.bill<=b.bill
and not exists (
select 1 from @test t where not exists (select 1 from @test where type=t.type and bill=t.Bill+1)
and type=b.type and bill<b.bill and bill>=a.bill
)
and c.type=a.type
and c.bill between a.bill and b.bill
group by a.type,a.bill,b.bill

--结果
type start       end         sum(free)   
---- ----------- ----------- ----------- 
A    1           4           500
A    9           9           500
B    12          12          200
C    1           1           50
C    3           4           350

（所影响的行数为 5 行）

[mugua604]

那几个加起来??

[mugua604]

.....
怎么分都给我了???

[b哈利路亚d]

强人给分

[b哈利路亚d]

。。。csnd怎么搞的，我明明给楼上的啊。。。

[b哈利路亚d]

晕，对不住上面高人了。。。

[-狙击手-]

select 
	a.type,
	min(a.bill) as min_bill ,
	c.max_bill,
	sum(free) as sum_free
from
(select 
	type,
	bill,
	(select min(bill) 
	 from Ta b 
	 where b.type=a.type and b.bill>=a.bill
		   and not exists(select 1 
							from Ta 
							where type=b.type and bill=b.bill+1)) as max_bill
from Ta a ) c
left join ta a on c.type = a.type and a.bill = c.bill
group by a.type,c.max_bill
order by type

[dobear_0922]

--> 测试数据：@test
declare @test table([type] varchar(1),[bill] int,[free] int)
insert @test
select 'A',1,50 union all
select 'A',2,100 union all
select 'A',3,150 union all
select 'A',4,200 union all
select 'A',9,500 union all
select 'B',12,200 union all
select 'C',1,50 union all
select 'C',3,150 union all
select 'C',4,200

;with b as
(select * from @test t where not exists(select 1 from @test where [type]=t.[type] and [bill]=t.[bill]+1))
select a.[type], start=min(a.[bill]), [end]=max(a.[bill]), [sum(free)]=sum(a.[free]) 
from @test a join b on a.[type]=b.[type] -- and a.[bill]<=b.[bill]
where b.[bill] = (select min([bill]) from b t where [type]=b.[type] and [bill]>=a.[bill])
group by a.[type], b.[bill]
order by a.[type], start

/*
type start       end         sum(free)
---- ----------- ----------- -----------
A    1           4           500
A    9           9           500
B    12          12          200
C    1           1           50
C    3           4           350

(5 行受影响)
*/

[yupengfei]

提供一个小思路求连续区间供参考。

(
select type, bill start from test a
where not exist ( select 1 from test b where b.bill = a.bill - 1)
order by bill
) billStart,
(
select type, bill end from test a
where not exist ( select 1 from test b where b.bill = a.bill + 1)
order by bill) billEnd