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 ondeclare @temp table (k1 int> 运行结果如下：
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 @maxcount0) 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
页:
[1]