3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
一个循环问题。
一个表：
id name
1 张三
2 李四
3 吴奖德
4 冯仁坤
. .
. .
. .
需要做循环一次后如下：
id name
1 冯仁坤
2 张三
3 李四
4 吴奖德
. .
. .
. .
需要在存储过程中做 ，不知道如何做啊?
平台：sql server 7.0 + delphi 5.0
在线等待，解答即给分。
id name
1 张三
2 李四
3 吴奖德
4 冯仁坤
. .
. .
. .
需要做循环一次后如下：
id name
1 冯仁坤
2 张三
3 李四
4 吴奖德
. .
. .
. .
需要在存储过程中做 ，不知道如何做啊?
平台：sql server 7.0 + delphi 5.0
在线等待，解答即给分。
...全文
请发表友善的回复…
发表回复
愉快的登山者 2003-01-17
declare @id int
declare @max int
declare @name char(10)
select @max = max(id) from tablename
set @id = @max
select @name = name from tablen where id = @id
while @id > 1 then
begin
update tablename set A.name = B.name from tablename as A , tablename as B where A.id = @id and B.id = @id -1
set @id = @id - 1
end
update tablename set name = @name where id = 1
declare @max int
declare @name char(10)
select @max = max(id) from tablename
set @id = @max
select @name = name from tablen where id = @id
while @id > 1 then
begin
update tablename set A.name = B.name from tablename as A , tablename as B where A.id = @id and B.id = @id -1
set @id = @id - 1
end
update tablename set name = @name where id = 1
yvqq 2003-01-17
yelook的方法最好，效率最高
hjhing 2003-01-17
create procedure UpdateRs
as
declare @max int
select @max= max[id] from Utable
select (case when [id]< @max then [id]+1 when [id] = @max then 1 else [id]-1 end) as tid,[name]
into #T
from Utable order by tid
update Utable
set [name] = #T.[name]
from #T
where [id] = #T.tid
as
declare @max int
select @max= max[id] from Utable
select (case when [id]< @max then [id]+1 when [id] = @max then 1 else [id]-1 end) as tid,[name]
into #T
from Utable order by tid
update Utable
set [name] = #T.[name]
from #T
where [id] = #T.tid
lumina 2003-01-17
一个表：
id name
1 张三
2 李四
3 吴奖德
4 冯仁坤
需要做循环一次后如下：
id name
1 冯仁坤
2 张三
3 李四
4 吴奖德
我需要的是把name字段的值循环，最上的往下放，最下的放在上面。
To：yelook(雨枫) 、nielisheng(阿土)兄，你们的代码没有循环啊。
id name
1 张三
2 李四
3 吴奖德
4 冯仁坤
需要做循环一次后如下：
id name
1 冯仁坤
2 张三
3 李四
4 吴奖德
我需要的是把name字段的值循环，最上的往下放，最下的放在上面。
To：yelook(雨枫) 、nielisheng(阿土)兄，你们的代码没有循环啊。
nielisheng 2003-01-17
create procedure as
declare @name1 varchar(20)
declare @name2 varchar(20)
declare @mid int
select identity(int, 2, 1) as aaid,* into @Tmp from table
select @mid = (select max(id) from #Tmp)
select @name1 = (select name from #Tmp where aaid = 1)
select @name2 = (select name from #Tmp where aaid = @mid)
update table set name = @name2 where id = 1
update table set name = @name1 where id = @mid
drop table table
select * into table from @Tmp
go
declare @name1 varchar(20)
declare @name2 varchar(20)
declare @mid int
select identity(int, 2, 1) as aaid,* into @Tmp from table
select @mid = (select max(id) from #Tmp)
select @name1 = (select name from #Tmp where aaid = 1)
select @name2 = (select name from #Tmp where aaid = @mid)
update table set name = @name2 where id = 1
update table set name = @name1 where id = @mid
drop table table
select * into table from @Tmp
go
yelook 2003-01-17
declare @name varchar(20)
select @name=top 1 name from table order by id desc
update table set name=a.name from table a where id=a.id+1
update table set name=@name where id=1
select @name=top 1 name from table order by id desc
update table set name=a.name from table a where id=a.id+1
update table set name=@name where id=1
CABO 2003-01-17
得到结果的逻辑是什么阿？
hjhing 2003-01-17
什麼規律啊，說清楚點先
qianxh 2003-01-17
如果id值在区间内是顺序连续的, 且记录除了id以外其余字段全部需要交换, 则:
declare @min_id int
declare @max_id int
select @min_id = min(id), @max_id = max(id) from table
update table set id = (id + 1) mod (@max_id + 1) + @min_id
go
declare @min_id int
declare @max_id int
select @min_id = min(id), @max_id = max(id) from table
update table set id = (id + 1) mod (@max_id + 1) + @min_id
go
lumina 2003-01-17
To yelook兄，代码提示top附近有错误，我用的是sql server 7.0。
我想好像没有问题啊。但是提示却......
我想好像没有问题啊。但是提示却......