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
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
#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