安全提示:腾讯公司提醒您警惕“QQ中奖”骗局。 让缘随风 20:44:04
ID N
1 800
2 820
3 830
4 500
5 800
6 820
我想查出800后连续大于800的数,如果小于800停止,从下一个800开始,如何实现?实现结果如下图:
ID N
2 820
3 830
6 820
ID N
1 800
2 820
3 830
4 500
5 800
6 820
我想查出800后连续大于800的数,如果小于800停止,从下一个800开始,如何实现?实现结果如下图:
ID N
2 820
3 830
6 820
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[N] int)
insert [tb]
select 1,800 union all
select 2,820 union all
select 3,830 union all
select 4,500 union all
select 5,800 union all
select 6,820select * from [tb] where [N]>800
/*
ID N
----------- -----------
2 820
3 830
6 820(3 行受影响)*/
DROP TABLE tb
GO
CREATE TABLE tb(id INT, n INT)
GO
INSERT tb SELECT 1, 800
UNION ALL SELECT 2, 820
UNION ALL SELECT 3, 830
UNION ALL SELECT 4, 500
UNION ALL SELECT 5, 870
UNION ALL SELECT 6, 800
UNION ALL SELECT 7, 820
GO
SELECT * FROM tb a
WHERE n>800
AND EXISTS(SELECT 1 FROM tb b
WHERE id<a.id AND n=800
AND NOT EXISTS
(SELECT 1 FROM tb
WHERE id < a.id AND id > b.id
AND n<800)
)/*
2 820
3 830
7 820
*/
insert into tb values(1 , 800)
insert into tb values(2 , 820)
insert into tb values(3 , 830)
insert into tb values(4 , 500)
insert into tb values(5 , 800)
insert into tb values(6 , 820)
create table tmp(ID int, N int)
godeclare @id1 int,@id2 int,@N int,@flag int;
set @flag = 0
declare cur cursor fast_forward for
select id,n from tb;
open cur;
fetch next from cur into @id1,@n;
while @@fetch_status=0
begin
if @N = 800 set @flag = 1
if @N < 800 set @flag = 0
if @N > 800 and @flag = 1 insert into tmp select @id1 , @N
fetch next from cur into @id1,@N;
end
close cur;
deallocate cur;select * from tmpdrop table tb ,tmp/*
ID N
----------- -----------
2 820
3 830
6 820(所影响的行数为 3 行)
*/