借帖子中的问题 http://topic.csdn.net/u/20080916/08/fde28e86-7c54-4d8b-8e27-542f4bbff720.html，回忆SQL Server 大师级人物 Ken Henderson
问题如下：
数据结构
a b c
1 x1 11
2 x2 15
3 x3 15
4 x3 14
5 x2 15
6 x4 13
第一条sql,统计列b中有几种情况(题中有x1,x2,x3,x4共四种)
第二条,查询C最大值的最大连续记录集(查询出2和3行),好像有点难,这条给50分。
参考大师Ken Henderson 的思路，编写的代码，虽然看上去不容易懂，但确实精彩，精妙绝伦：
set nocount on
declare @temp table (k1 int identity,
b varchar(10),
c1 int)
insert into @temp values('x1',11)
insert into @temp values('x2',13)
insert into @temp values('x3',15)
insert into @temp values('x3',15)
insert into @temp values('x2',12)
insert into @temp values('x9',10)
insert into @temp values('x1',11)
insert into @temp values('x6',15)
insert into @temp values('x3',15)
insert into @temp values('x5',12)
declare @max int = (select max(c1) from @temp)
declare @temp_max table(starts int,ends int,diff int )
insert into @temp_max
select starts=v.k1,
ends=isnull(
min(case when v.k1<a.k1 and a.c1<>@max
then a.k1
else null
end )-1,
max(case when v.k1<a.k1
then a.k1
else v.k1
end)
),
isnull(
min(case when v.k1<a.k1 and a.c1<>@max
then a.k1
else null
end)-1,
max(case when v.k1<a.k1
then a.k1
else v.k1
end)
)-v.k1 as diff
from @temp v
inner join @temp a
on v.c1=@max
group by v.k1
having isnull(
min(case when v.k1<a.k1 and a.c1<>@max
then a.k1
else null
end)-1,
max(case when v.k1<a.k1
then a.k1
else v.k1
end)
) >=v.k1
and
isnull(
max(case when v.k1>a.k1 and a.c1<>@max
then a.k1
else null
end) + 1,
min(case when v.k1>a.k1
then a.k1
else v.k1
end)
) = v.k1
select *
from @temp t
where exists (
select *
from @temp_max
where diff=(select max(diff)
from @temp_max)
and (t.k1 between starts and ends)
)
运行结果如下：
k1 b c1
-----------
3 x3 15
4 x3 15
8 x6 15
9 x3 15
这是一开始我自己写得代码，挺长的，是用了双层的游标：
create table tablename(a int ,b varchar(10),c int)
insert into tablename values(1, 'x1 ',11)
insert into tablename values(2, 'x2 ',15)
insert into tablename values(3, 'x3 ',15 )
insert into tablename values(4, 'x3 ',14 )
insert into tablename values(5, 'x2 ',15 )
insert into tablename values(6, 'x4 ',13 )
insert into tablename values(7, 'x4 ',15 )
insert into tablename values(8, 'x6 ',15 )
insert into tablename values(9, 'x4 ',15 )
declare @temp_max table(a int ,b varchar(10),c int)
declare @temp table(a int ,b varchar(10),c int)
declare @maxcount int
set @maxcount=0
declare @max_c int
select @max_c=max(c) from tablename
declare @recordcount int
set @recordcount=0
declare @a int
declare @b varchar(10)
declare @c int
declare max_cousor cursor for
select * from tablename
open max_cousor
fetch max_cousor into @a,@b,@c
while @@fetch_status =0
begin
if(@c=@max_c)
begin
insert into @temp values(@a,@b,@c)
set @recordcount=@recordcount+1
fetch max_cousor into @a,@b,@c
while @@fetch_status=0
begin
if(@c=@max_c)
begin
insert into @temp values(@a,@b,@c)
fetch max_cousor into @a,@b,@c
set @recordcount=@recordcount+1
end
else
begin
break
end
end
end
if(@maxcount=@recordcount and @maxcount <> 0)
begin
if(@maxcount> =1)
insert into @temp_max values(null,null,null)
insert into @temp_max
select * from @temp
set @recordcount=0
delete from @temp
end
else
begin
if(@maxcount <@recordcount)
begin
delete from @temp_max
insert into @temp_max
select * from @temp
delete from @temp
set @maxcount=@recordcount
set @recordcount=0
end
else
set @recordcount=0
delete from @temp
end
fetch max_cousor into @a,@b,@c
end
close max_cousor
deallocate max_cousor
select a,b,c,@maxcount as '连续的个数 ' from @temp_max
最近又想到一种办法：
set nocount on
declare @temp table (k1 int identity,
b varchar(10),
c1 int)
insert into @temp values('x1',11)
insert into @temp values('x2',13)
insert into @temp values('x3',15)
insert into @temp values('x3',15)
insert into @temp values('x3',15)
insert into @temp values('x2',12)
insert into @temp values('x9',10)
insert into @temp values('x1',11)
insert into @temp values('x6',15)
insert into @temp values('x3',15)
insert into @temp values('x3',15)
insert into @temp values('x5',12)
declare @max int = (select max(c1) from @temp)
;with tt
as
(
select t.k1,
t.c1,
t.b,
ROW_NUMBER() over(partition by c1
order by k1) rnum
from @temp t
where t.c1 = @max
),
ttt
as
(
select t1.k1,
T1.rnum AS R1,
T2.k1 AS K2,
T2.rnum AS R2,
t2.k1-t1.k1 as diff
From tt t1
inner join tt t2
on T1.K1 < T2.K1
and t1.rnum <> t2.rnum
WHERE t2.k1 - t1.k1 = t2.rnum - t1.rnum --两者之间的差值相等
),
diff
as
(
select max(diff) as maxDiff
from ttt
)
select t.*
from @temp t
inner join ttt
on t.k1 between ttt.k1 and ttt.K2
inner join diff d
on d.maxDiff = ttt.diff