SQL Server 大师级人物 Ken Henderson

 借帖子中的问题 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