10、SQL Server：sql语句双重循环_sql server 双层循环-优快云博客

本文提供了一种使用SQL脚本的方法来更新数据库表中的shunxu字段，使其按照lanmu_id和qishiye两个条件自增排序。适用于需要重新整理记录顺序的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文来自：http://www.2cto.com/database/201502/377072.html

表 j_wenzhang_aps201503 中 shunxu 字段为null。现在想根据 lanmu_id,qishiye两项，更新shunxu 字段。

1 、如果让shunxu 字段自增，不存在重复，且lanmu_id 较小的，对应的 shunxu 也小；lanmu_id 相同，qishiye较小的，对应的shunxu也小。

 
        declare 
         @maxid 
        int
       
 
        declare 
         @minid 
        int
       
 
        declare 
         @shunxu 
        int
       
 
        declare 
         @maxqishiye 
        int
       
 
        declare 
         @minqishiye 
        int
       

          
       
 
        select 
         @maxid= 
        max
        (lanmu_id) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null
       
 
        select 
         @minid= 
        min
        (lanmu_id) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null
       
 
        select 
         @shunxu=0
       
 
        if ((@maxid 
        is 
         not 
         null
        ) 
        and 
         (@minid 
        is 
         not 
         null
        ))
       
 
        begin
       
 
        while (@maxid>=@minid) 
       
 
        begin
       
 
        select 
         @maxqishiye=
        max
        (qishiye) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null 
         and 
         lanmu_id=@minid
       
 
        select 
         @minqishiye=
        min
        (qishiye) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null 
         and 
         lanmu_id=@minid
       

          
       
 
        if ((@maxqishiye 
        is 
         not 
         null
        ) 
        and 
         (@minqishiye 
        is 
         not 
         null
        ))
       
 
        begin
       
 
        while (@maxqishiye>=@minqishiye) 
       
 
        begin
       

          
       
 
        select 
         @shunxu=@shunxu+1
       
 
        update 
         j_wenzhang_aps201503  
        set 
         shunxu=@shunxu 
        where 
         lanmu_id=@minid 
        and 
         qishiye=@minqishiye 
        and 
         shunxu 
       

          
       
 
        is 
         null
       

          
       
 
        select 
         @minqishiye=
        min
        (qishiye) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null 
         and 
         lanmu_id=@minid
       
 
        end
       
 
        end
       
 
        select 
         @minid=
        min
        (lanmu_id) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null
       
 
        end
       
 
        end
       
 
        go
       

执行后结果：

加载中...

2、如果按照栏目让shunxu 字段自增，且lanmu_id 较小的，对应的 shunxu 也小；lanmu_id 相同，qishiye较小的，对应的shunxu也小。

即（每个相同lanm_id里，shunxu 都从1 开始增加）

 
        declare 
         @maxid 
        int
       
 
        declare 
         @minid 
        int
       
 
        declare 
         @shunxu 
        int
       
 
        declare 
         @maxqishiye 
        int
       
 
        declare 
         @minqishiye 
        int
       

          
       
 
        select 
         @maxid= 
        max
        (lanmu_id) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null
       
 
        select 
         @minid= 
        min
        (lanmu_id) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null
       

          
       
 
        if ((@maxid 
        is 
         not 
         null
        ) 
        and 
         (@minid 
        is 
         not 
         null
        ))
       
 
        begin
       
 
        while (@maxid>=@minid) 
       
 
        begin
       
 
        select 
         @maxqishiye=
        max
        (qishiye) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null 
         and 
         lanmu_id=@minid
       
 
        select 
         @minqishiye=
        min
        (qishiye) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null 
         and 
         lanmu_id=@minid
       

          
       
 
        select 
         @shunxu=0
       
 
        if ((@maxqishiye 
        is 
         not 
         null
        ) 
        and 
         (@minqishiye 
        is 
         not 
         null
        ))
       
 
        begin
       
 
        while (@maxqishiye>=@minqishiye) 
       
 
        begin
       

          
       
 
        select 
         @shunxu=@shunxu+1
       
 
        update 
         j_wenzhang_aps201503  
        set 
         shunxu=@shunxu 
        where 
         lanmu_id=@minid 
        and 
         qishiye=@minqishiye 
        and 
         shunxu 
        is 
         null
       

          
       
 
        select 
         @minqishiye=
        min
        (qishiye) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null 
         and 
         lanmu_id=@minid
       
 
        end
       
 
        end
       
 
        select 
         @minid=
        min
        (lanmu_id) 
        from 
         j_wenzhang_aps201503 
        where 
         shunxu 
        is 
         null
       
 
        end
       
 
        end
       
 
        go
       

        
 
       