sql語句雙重迴圈，sql語句

sql語句雙重迴圈，sql語句

表 j_wenzhang_aps201503 中 shunxu 欄位為null。現在 想根據 lanmu_id,qishiye兩項，更新shunxu 欄位。

1 、如果讓shunxu 欄位 自增，不存在重複，且lanmu_id 較小的，對應的 shunxu 也小；lanmu_id 相同，qishiye較小的，對應的shunxu也小。

declare @maxid int declare @minid intdeclare @shunxu intdeclare @maxqishiye intdeclare @minqishiye intselect @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=0if ((@maxid is not null) and (@minid is not null))beginwhile (@maxid>=@minid) beginselect @maxqishiye=max(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidif ((@maxqishiye is not null) and (@minqishiye is not null))beginwhile (@maxqishiye>=@minqishiye) beginselect @shunxu=@shunxu+1update j_wenzhang_aps201503  set shunxu=@shunxu where lanmu_id=@minid and qishiye=@minqishiye and shunxu is nullselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidendendselect @minid=min(lanmu_id) from j_wenzhang_aps201503 where shunxu is null endendgo

執行後結果：

2、如果按照欄目 讓shunxu 欄位 自增，且lanmu_id 較小的，對應的 shunxu 也小；lanmu_id 相同，qishiye較小的，對應的shunxu也小。

即（每個相同lanm_id裡，shunxu 都從1 開始增加）

declare @maxid int declare @minid intdeclare @shunxu intdeclare @maxqishiye intdeclare @minqishiye intselect @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))beginwhile (@maxid>=@minid) beginselect @maxqishiye=max(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidselect @shunxu=0if ((@maxqishiye is not null) and (@minqishiye is not null))beginwhile (@maxqishiye>=@minqishiye) beginselect @shunxu=@shunxu+1update j_wenzhang_aps201503  set shunxu=@shunxu where lanmu_id=@minid and qishiye=@minqishiye and shunxu is nullselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidendendselect @minid=min(lanmu_id) from j_wenzhang_aps201503 where shunxu is null endendgo

執行後結果：