在SQL中使用单个游标比较两行 [英] Compare two rows using a single cursor in SQL

问题描述

我有以下表格

  + ------- + ------- + --------- + ------ + 
 | icode | iname | icatcode | slno | 
 + ------- + ------- + ---------- + ------ + 
 | 10 | a | 11 | 0 | 
 | 20 | b | 31 | 0 | 
 | 30 | c | 11 | 0 | 
 | 40 | d | 21 | 0 | 
 | 50 | e | 31 | 0 | 
 | 60 | f | 11 | 0 | 
 | 70 | g | 21 | 0 | 
 | 80 | h | 41 | 0 | 
 + ------- + ------- + ---------- + ------ + 
  
 
 
 
我需要使用光标更新 slno 列。 o / p应该是下表，即当 icatcode 相同时，它应该增加 slno ，当 icatcode 将 slno 更改为1。
  + ------- + -  + ---------- + ------ + 
 | icode | iname | icatcode | slno | 
 + ------- + ------- + ---------- + ------ + 
 | 10 | a | 11 | 1 | 
 | 30 | b | 11 | 2 | 
 | 60 | c | 11 | 3 | 
 | 70 | d | 21 | 1 | 
 | 40 | e | 21 | 2 | 
 | 50 | f | 31 | 1 | 
 | 20 | g | 31 | 2 | 
 | 80 | h | 41 | 1 | 
 + ------- + ------- + ---------- + ------ + 
  
我写了查询
  @icode int，@ iccode int，@ islno int，@ inccode int 
 
 set @ islno = 1 
 
声明cur2光标用于选择icode，iccode从im order by iccode 
 
 open cur2 
 
接下来从cur2进入@ icode，@ iccode 
 
 while @@ FETCH_STATUS = 0 
 
 begin 
 
 update im set slno = @ islno其中@ icode = icode 
接下来从cur2获取到@ icode，@ inccode 
如果@ iccode<> @inccode 
 begin 
 set @ islno = 1 
 end 
 else 
 begin 
 set @ islno = @ islno + 
 end 
 
 end 
 
 close cur2 
 deallocate cur2 
  
上述查询结果如下o / p 
  + ------- + ------- + ------ ---- + ------ + 
 | icode | iname | icatcode | slno | 
 + ------- + ------- + ---------- + ------ + 
 | 10 | a | 11 | 1 | 
 | 20 | b | 31 | 1 | 
 | 30 | c | 11 | 2 | 
 | 40 | d | 21 | 1 | 
 | 50 | e | 31 | 1 | 
 | 60 | f | 11 | 3 | 
 | 70 | g | 21 | 1 | 
 | 80 | h | 41 | 1 | 
 + ------- + ------- + ---------- + ------ + 
  
我需要做哪些变更，才能取得所需的o / p？ 
解决方案
在SQLServer中，您可以尝试此
  DECLARE @icode int，
 @icatcodeOld int，
 @icatcode int，
 @slno int = 1 
 
 DECLARE cur2 CURSOR 
 FOR 
 SELECT icode，icatcode 
 FROM im 
 ORDER BY icatcode ASC 
 OPEN cur2 
 FETCH NEXT FROM cur2 INTO @icode ，@icatcode 
 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
 UPDATE im 
 SET slno = @slno 
 WHERE icode = @icode 
 
 SET @icatcodeOld = @icatcode 
 FETCH NEXT FROM cur2 INTO @icode，@icatcode 
 SELECT @slno = CASE WHEN @icatcodeOld = @icatcode THEN @slno + 1 ELSE 1 END 
 END 
 
关闭cur2 
 DEALLOCATE cur2 
  
演示  SQLFiddle   
 
I have the following table
+-------+-------+----------+------+
| icode | iname | icatcode | slno |
+-------+-------+----------+------+
|    10 | a     |       11 |    0 |
|    20 | b     |       31 |    0 |
|    30 | c     |       11 |    0 |
|    40 | d     |       21 |    0 |
|    50 | e     |       31 |    0 |
|    60 | f     |       11 |    0 |
|    70 | g     |       21 |    0 |
|    80 | h     |       41 |    0 |
+-------+-------+----------+------+
I need to update the slno column using a cursor. The o/p should be the following table ie., when the icatcode is same it should increment the slno and when icatcode changes it should set the slno to 1.
+-------+-------+----------+------+
| icode | iname | icatcode | slno |
+-------+-------+----------+------+
|    10 | a     |       11 |    1 |
|    30 | b     |       11 |    2 |
|    60 | c     |       11 |    3 |
|    70 | d     |       21 |    1 |
|    40 | e     |       21 |    2 |
|    50 | f     |       31 |    1 |
|    20 | g     |       31 |    2 |
|    80 | h     |       41 |    1 |
+-------+-------+----------+------+
I have written the query for it
declare @icode int,@iccode int,@islno int,@inccode int

set @islno=1

declare cur2 cursor for select icode,iccode from im order by iccode

open cur2

fetch next from cur2 into @icode,@iccode

while @@FETCH_STATUS=0

begin

    update im  set slno=@islno where @icode=icode
    fetch next from cur2 into @icode,@inccode
    if @iccode<>@inccode
    begin
    set @islno=1
    end
    else
    begin
    set @islno=@islno+
    end

end

close cur2
deallocate cur2
The above query results the following o/p
+-------+-------+----------+------+
| icode | iname | icatcode | slno |
+-------+-------+----------+------+
|    10 | a     |       11 |    1 |
|    20 | b     |       31 |    1 |
|    30 | c     |       11 |    2 |
|    40 | d     |       21 |    1 |
|    50 | e     |       31 |    1 |
|    60 | f     |       11 |    3 |
|    70 | g     |       21 |    1 |
|    80 | h     |       41 |    1 |
+-------+-------+----------+------+
What changes do I need to do so that I will get the desired o/p? I need to do this  only by using cursors.
解决方案
In SQLServer you can try this
DECLARE @icode int,
        @icatcodeOld int,
        @icatcode int,
        @slno int = 1                   

DECLARE cur2 CURSOR
  FOR
    SELECT icode, icatcode
    FROM im    
    ORDER BY icatcode ASC        
  OPEN cur2
  FETCH NEXT FROM cur2 INTO @icode, @icatcode
  WHILE @@FETCH_STATUS = 0
    BEGIN      
      UPDATE im
      SET slno = @slno                  
      WHERE icode = @icode                 

      SET @icatcodeOld = @icatcode                                                             
      FETCH NEXT FROM cur2 INTO @icode, @icatcode      
      SELECT @slno = CASE WHEN @icatcodeOld = @icatcode THEN @slno + 1 ELSE 1 END
    END

  CLOSE cur2
  DEALLOCATE cur2
Demo on SQLFiddle

                        
这篇关于在SQL中使用单个游标比较两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！