sql语句双重循环

最新推荐文章于 2023-12-12 10:47:45 发布

csdn_xhl868

最新推荐文章于 2023-12-12 10:47:45 发布

阅读量1.8k

点赞数

分类专栏：数据库sql

本文链接：https://blog.csdn.net/csdn_xhl868/article/details/43736397

版权

数据库sql 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

表 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

执行后结果：