sql语句双重循环

来源：互联网发布：java虚拟机怎么算编辑：程序博客网时间：2024/05/01 12:13

表 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

执行后结果：

0 0