一个表 A , 有列 lv sc1 sc2 , 都是 int 字段现在按变量n 输出前n条 (按lv排序), 且 每行与上下行之间的 sc1 之差不小于100, sc2 之差不小于200
解决方案 »
- 一个SQL触发器的问题。
- 求一个比较特殊的多表连接的SQL语句
- SELECT CHARINDEX('|', {fn REPLACE('aa,bb,cc', ',', '|')}) 中 fn 的是什么意思?
- 如何通过视图的别名获得原表的字段名
- 求一统计语句!
- 挑战专家:哪个存储过程占用了太多内存?
- 如何利用SQL2005实现各站点数据同步?
- 关于SQL长度
- 请问在修改作业里,报“作业已将XXX作为服务器,作业不能保存”,是什么原因?
- in which state you can re-create control file
- 紧急求解----navicat恢复问题
- sql2005关于日期函数的问题(本人刚入门,请朋友指教)
( @n int
)
as
begin
declare @id int, @lv int, @sec1 int , @sc1 int, @count int, @row int
declare @pre_id int, @pre_lv int, @pre_sec1 int , @pre_sc1 int
create table #temp(id int identity,lv int, sec1 int, sc1 int)
create table #return(lv int, sec1 int, sc1 int)
insert into #temp(lv , sec1 , sc1 )
select lv, sec1, sc1
from a
order by lv, sec1, sc1
declare cur_1 cursor for
select id, lv, sec1, sc1
from #temp
open cur_1
fetch cur_1 into @id, @lv, @sec1, @sc1
set @count=1
set @row=1
while (@@sqlstatus != 2 )
begin
if @row=1
begin
set @pre_id=@id
set @pre_lv=@lv
set @pre_sec1=@sec1
set @pre_sc1=@sc1
set @row =2
fetch cur_1 into @id, @lv, @sec1, @sc1
end
else
begin
if (@sec1 - @pre_sec1) > 100 and (@sc1 - @pre_sc1) > 200
begin if @count <=@n
begin
insert into #return(lv, sec1,sc1) values(@lv,@sec1,@sc1)
set @count = @count+1
set @pre_id=@id
set @pre_lv=@lv
set @pre_sec1=@sec1
set @pre_sc1=@sc1
end
end
fetch cur_1 into @id, @lv, @sec1, @sc1
end
end
close cur_1
deallocate cur_1
select * from #return
drop table #temp
drop table #returnend