java二重循环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 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

执行后结果：

标签：