10、SQL Server：sql语句双重循环

本文来自：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也小。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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 开始增加）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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