使用SQL游标而不是自联接来查找非连续数字

In SQL Server, I would like to check if numbers in a specific column are following each other in ascending order using SQL Cursor Functions. Basically each row must have a greater value than the previous one. I have written a code using self-join as shown below:

在SQL Server中,我想检查特定列中的数字是否使用SQL游标函数按升序互相跟随。基本上每行必须具有比前一行更大的值。我使用自联接编写了一个代码,如下所示:

First I have given each row a row number that is defined as integer, in order to compare each row with the previous one using select row_number() over(ORDER BY (SELECT NULL) ) AS Row#. Then compared the table with itself using the row number created above and found the rows that are not consecutive using the code below.

首先,我给每一行定义为整数的行号,以便使用select row_number()over(ORDER BY(SELECT NULL))AS Row#将每一行与前一行进行比较。然后使用上面创建的行号将表与自身进行比较,并使用下面的代码找到不连续的行。

  SELECT *
  FROM Sequence AS prev_row
  JOIN Sequence AS next_row 
  ON prev_row.Row#+1 = next_row.Row#
  AND (prev_row.Number > next_row.Number)

Even though my code above is working fine, I wanted to perform the same operation using SQL Cursors to have a better understanding of usage of cursors and develop a different approach on SQL methods. So I wrote the following code, but it is not working:

即使上面的代码工作正常,我也希望使用SQL游标执行相同的操作,以便更好地理解游标的使用并在SQL方法上开发不同的方法。所以我编写了以下代码,但它不起作用:

DECLARE db_cursor CURSOR 
FOR
SELECT Row#, Number
FROM Sequence;

DECLARE @Row# int;
DECLARE @Numbers int;

OPEN db_cursor
FETCH NEXT FROM db_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Row# , @Numbers
    FETCH NEXT FROM db_cursor
    SELECT @Row#, @Numbers --this part is for printing results
    where @Row#>@Row#+1 --condition
END
CLOSE db_cursor;
DEALLOCATE db_cursor; 

Can someone explain where I go wrong? Help will be appreciated.

有人可以解释我哪里出错吗?帮助将不胜感激。

1 个解决方案

#1

create table seq(id int, val int);
insert into seq values
(1,  10),
(2,  11),
(3,  15),
(4,  20),
(5,  19),
(6,  24),
(7,  30),
(8,  31),
(9,  29),
(10, 35);
GO

10 rows affected

select id, val,
       iif(coalesce(lag(val) over (order by id), val) <= val, 'OK', 'ERROR') [check]
from seq
GO

id | val | check
-: | --: | :----
 1 |  10 | OK   
 2 |  11 | OK   
 3 |  15 | OK   
 4 |  20 | OK   
 5 |  19 | ERROR
 6 |  24 | OK   
 7 |  30 | OK   
 8 |  31 | OK   
 9 |  29 | ERROR
10 |  35 | OK   

declare cur cursor for
    select id, val from seq order by id;

declare @id int = 0,
        @val int = 0,
        @last_val int = null;

declare @res table (id int, val int, [check] varchar(10));

open cur;

fetch next from cur into @id, @val

while @@fetch_status = 0
begin
    insert into @res
    select @id, @val, iif (@last_val is null or @last_val < @val, 'OK', 'ERROR');

    set @last_val = @val;    

    fetch next from cur into @id, @val;
end

close cur;
deallocate cur;

select * from @res;
GO

id | val | check
-: | --: | :----
 1 |  10 | OK   
 2 |  11 | OK   
 3 |  15 | OK   
 4 |  20 | OK   
 5 |  19 | ERROR
 6 |  24 | OK   
 7 |  30 | OK   
 8 |  31 | OK   
 9 |  29 | ERROR
10 |  35 | OK   

dbfiddle here