--> 测试数据: @s declare @s table (menuID int,MenuName varchar(5),MenuUPID int,menuOrder int) insert into @s select 1,'系统1',0,1 union all select 4,'系统2',0,4 union all select 6,'系统3',0,6 union all select 8,'系统4',0,8 union all select 9,'系统5',0,9 update @s set menuOrder=case when (select top 1 menuOrder from @s where menuID<a.menuID order by menuID desc) is not null then (select top 1 menuOrder from @s where menuID<a.menuID order by menuID desc) else (select top 1 menuOrder from @s order by menuID desc) end from @s a select * from @s
本帖最后由 zjcxc 于 2008-10-09 10:35:40 编辑
declare @s table (menuID int,MenuName varchar(5),MenuUPID int,menuOrder int) insert into @s select 1,'系统1',0,1 union all select 4,'系统2',0,4 union all select 6,'系统3',0,6 union all select 8,'系统4',0,8 union all select 9,'系统5',0,9 DECLARE @menuOrder_p int, @menuOrder int;
UPDATE @s SET menuOrder = @menuOrder_p, @menuOrder_p = @menuOrder, @menuOrder = menuOrder;UPDATE @s SET menuOrder = @menuOrder WHERE menuOrder IS NULL;select @menuOrder, @menuOrder_p SELECT * FROM @s
declare @s table (menuID int,MenuName varchar(5),MenuUPID int,menuOrder int)
insert into @s
select 1,'系统1',0,1 union all
select 4,'系统2',0,4 union all
select 6,'系统3',0,6 union all
select 8,'系统4',0,8 union all
select 9,'系统5',0,9
update @s set menuOrder=case when (select top 1 menuOrder from @s where menuID<a.menuID order by menuID desc) is not null
then (select top 1 menuOrder from @s where menuID<a.menuID order by menuID desc) else
(select top 1 menuOrder from @s order by menuID desc) end
from @s a
select * from @s
insert into @s
select 1,'系统1',0,1 union all
select 4,'系统2',0,4 union all
select 6,'系统3',0,6 union all
select 8,'系统4',0,8 union all
select 9,'系统5',0,9
DECLARE
@menuOrder_p int,
@menuOrder int;
UPDATE @s SET
menuOrder = @menuOrder_p,
@menuOrder_p = @menuOrder,
@menuOrder = menuOrder;UPDATE @s SET
menuOrder = @menuOrder
WHERE menuOrder IS NULL;select @menuOrder, @menuOrder_p
SELECT * FROM @s