游标的效率以及case的妙用

此文章并非原创,但是注释部分是笔者自行添加的.

CREATE TABLE [#tmp](px int IDENTITY(1,1),value int)

大约有10000条记录，value 字段是一个随机的值，现要求是输入的一个值M，要找出前N条记录，且N条记录value的总和刚好不大M（意思是再多一条记录都会大于M）。

要求：语句运行时间要求在1秒种内完成
/*最后实验的时候我自己使用的50W条数据的记录,M值也取得很大*/

示例数据为：
px value
----------
5 5
4 12
7 9
1 3
3 6
6 23
2 15
则，录入20，找出：
px value
----------
1 3
2 15

1、
select a.* from #tmp a where (select sum(value) from #tmp where px<=a.px)<=@m --此法选数据其实很快，但却是流水作业，因此效率不高.结果在50W条测试数据的压力测试下，整个查询历时2m41s，与效率要求相去甚远 2、 于是，转而通过遍历表的方式实现查询： declare @px int,@s bigint,@m int set @s=0 set @m=20 update a set @px=case when @s<=@m then px else @px end, @s =@s+value from #t1 a select * from #t1 where px<@px order by px --此法对log有很大影响.虽然在50W条测试数据的情况下，效率基本满意，但是又出现了新的问题，一旦px字段不是按升序排列，查询的结果就不正确 3、 declare @px int,@value int,@sum int,@m int,@px1 int set @sum=0 set @m=20 declare t_cursor cursor for select px,value from #tmp order by px open t_cursor fetch next from t_cursor into @px,@value while @@fetch_status=0 begin if(@sum>@m)
break
set @sum=@sum+@value
set @px1=@px
fetch next from t_cursor into @px,@value
end
close t_cursor
deallocate t_cursor;
select * from #tmp where px<@px1 order by px;
--此法效率很高,但是看执行计划是非常非常慢的.起码说明了游标在特定情况下是有效率的,除了select * from tb

4、
declare @px int,@s int,@m int
set @s=0
set @m=20
select
@px=case when @s<=@m then px else @px end,
@s =@s+case when @s<=@m then value else 0 end
from
(select top 100 percent * from #tmp order by px) a
select * from #tmp where px<@px order by px
--效率很不错,其实是对方法2的改进
--利用case语句将update巧妙地换成select:@px=case when @s<=@m then px else @px end这句,select @px=@px其实是结束