sql排序问题

[huangwenya123]

表1：
Name Show_Order
A 1
B 2
C 3
D 4
E 5
变为表2：
Name Show_Order
A 1
D 2
B 3
C 4
E 5
或变为表3：
Name Show_Order
A 1
D 2
B 3
C 4
E 6
传入的参数只有D,B。

[让你望见影子的墙]

--> 测试数据: [c]
if object_id('[c]') is not null drop table [c]
create table [c] ([Name] varchar(1),[Show_Order] int)
insert into [c]
select 'A',1 union all
select 'B',2 union all
select 'C',3 union all
select 'D',4 union all
select 'E',5
;
with
wang as
(
select *,
 orderid=case when Name IN('D','B') then 2
              else 
                case when Name < all(select Name='D' union select Name='B') then 1 				 
					when Name >all(select Name='D' union select Name='B') then 4 
				else 3 end
			end
from c)

select Name,Show_Order=ROW_NUMBER() over(order by orderid ,name desc) 
from wang


A	1
D	2
B	3
C	4
E	5

[叶子]

如果传入C，E的话，就变成这种结果是不？ Name Show_Order A 1 B 2 C 3 E 4 D 5

[在路上_-]

create table [c] ([Name] varchar(1),[Show_Order] int);
go

insert into [c]
select 'A',1 union all
select 'B',2 union all
select 'C',3 union all
select 'D',4 union all
select 'E',5;

select * from c order by Show_Order;
go

create proc pResort
  @sc varchar(1),
  @tg varchar(1)
as

declare @sc_order int, @tg_order int;

select @sc_order= [Show_Order]
from c
where [Name]= @sc;

select @tg_order= [Show_Order]
from c
where [Name]= @tg;

update c set Show_Order=0
from c
where [Name]=@sc;

update c set Show_Order= Show_Order + 1
from c
where Show_Order>=@tg_order and Show_Order<@sc_order;

update c set Show_Order= @tg_order
where [Name]=@sc;
go

exec pResort 'D', 'B';

select * from c order by Show_Order;

drop proc pResort;
drop table [c];

[huangwenya123]

引用 1 楼 HEROWANG 的回复:

我将你的语句改为

if object_id('[c]') is not null drop table [c]
create table [c] ([Name] varchar(1),[Show_Order] int)
insert into [c]
select 'A',1 union all
select 'B',2 union all
select 'C',3 union all
select 'D',4 union all
select 'E',5
;
with
wang as
(
select *,
 orderid=case when Name IN('D','B') then (select Show_Order from c where Name='B')
              else 
                case when Name < all(select Name='D' union select Name='B') then (select Show_Order from c where Name='B')-1                  
                    when Name >all(select Name='D' union select Name='B') then (select Show_Order from c where Name='D') 
                else 3 end
            end
from c)
 
select Name,Show_Order=ROW_NUMBER() over(order by orderid ,name desc) 
from wang

当我把参数’B‘改为’A‘时，效果不是我想要的，大哥你能否写成动态的，因为我想做菜单拖拽效果。

[让你望见影子的墙]

那你总得说下，你的排序规则是什么吧。总不能让人去猜吧。

[huangwenya123]

引用 3 楼 xxzxwsx 的回复:

大哥我把你的代码改成了我需要的样子，你看看，还有谢谢你的提示，我结贴了。

if object_id('[c]') is not null drop table [c]
create table [c] ([Name] varchar(1),[Show_Order] int);
go
 
insert into [c]
select 'A',1 union all
select 'B',2 union all
select 'C',3 union all
select 'D',4 union all
select 'E',5;
 
select * from c order by Show_Order;
go
 
create proc pResort
  @sc varchar(1),
  @tg varchar(1)
as
 
declare @sc_order int, @tg_order int;
 
select @sc_order= [Show_Order]
from c
where [Name]= @sc;
 
select @tg_order= [Show_Order]
from c
where [Name]= @tg;
 
if(@sc_order > @tg_order)
begin
update c set Show_Order= Show_Order + 1
from c
where Show_Order>=@tg_order and Show_Order<@sc_order;
end

else if(@sc_order < @tg_order)
begin
update c set Show_Order=Show_Order-1
from c
where Show_Order<=@tg_order and Show_Order>@sc_order; 
end

update c set Show_Order= @tg_order
where [Name]=@sc;
go
 
exec pResort 'D', 'B';
 
select * from c order by Show_Order;
 
drop proc pResort;
drop table [c];

[huangwenya123]

引用 5 楼 HEROWANG 的回复:

那你总得说下，你的排序规则是什么吧。总不能让人去猜吧。

不好意思，是我的错，我结贴了。