虾米求sql语句

[lovezgs]

假设我有一个表，里面有两个字段，为id和num，类型都为int，求sql语句使num中的数字依次向上移
例如初始值为：
id num
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1
执行完sql语句后为
id num
1 9
2 8
3 7
4 6
5 5
6 4
7 3
8 2
9 1
10

语句要高效

[-狙击手-]

select id = (select count(1) from ta where num > = a.num),
       num
from ta a
order by id

[wzy_love_sly]

1	9
2	8
3	7
4	6
5	5
6	4
7	3
8	2
9	1
10	

[-狙击手-]

晕，看错了， 以为排序 呢

[-狙击手-]

select id,case num when 1 then '' else ltrim(num - 1) end as num
from ta

[cxmcxm]

--假设表名为t ,id号必须连续
update a
set num=b.num
from t a,t b where a.id=b.id-1

[cxmcxm]

declare @t table(id int,num int)
insert into @t values(1,10)
insert into @t values(2,9)
insert into @t values(3,8)
insert into @t values(4,7)
insert into @t values(5,6)
insert into @t values(6,5)
insert into @t values(7,4)
insert into @t values(8,3)
insert into @t values(9,2)
insert into @t values(10,1)

select * from @t

update a
set num=b.num
from @t a left join @t b on a.id=b.id-1

select * from @t

[-晴天]

[Quote=引用 8 楼 cxmcxm 的回复:]
SQL codedeclare @t table(id int,num int)
insert into @t values(1,10)
insert into @t values(2,9)
insert into @t values(3,8)
insert into @t values(4,7)
insert into @t values(5,6)
insert into @t values(6,5)
insert into @t values(7,4)
insert into @t values(8,3)
insert into @t values(9,2)
insert into @t values(10,1)

select * from @t

update a
set num=b.num
from @t a left join @t b on…
[/Quote]

正解.
其实楼主只是把第二列提上一行,与其值无关.试试这个:

declare @tb table (id int,num int)
insert into @tb select 1,10
insert into @tb select 2,9
insert into @tb select 3,10
insert into @tb select 4,7
insert into @tb select 5,6

insert into @tb select 6,5
insert into @tb select 7,7
insert into @tb select 8,3
insert into @tb select 9,2
insert into @tb select 10,1

--select id ,case when num-1 >0 then ltrim(num-1) else '' end as num from @tb
--select id = (select count(1) from @tb where num > = a.num),  num from @tb a order by id

update a set num=b.num from @tb a left join @tb b on a.id=b.id-1
select * from @tb
/*
id          num         
----------- ----------- 
1           9
2           10
3           7
4           6
5           5
6           7
7           3
8           2
9           1
10          NULL

其他几位大叔做得都不对.

[-晴天]

如果ID不连续,则要:

declare @tb table (id int,num int)
insert into @tb select 1,10
insert into @tb select 2,9
insert into @tb select 5,10
insert into @tb select 6,7
insert into @tb select 9,6

insert into @tb select 15,5
insert into @tb select 22,7
insert into @tb select 23,3
insert into @tb select 29,2
insert into @tb select 100,1

update a set num=b.num from @tb a left join @tb b on a.id<b.id

select * from @tb
/*
id          num         
----------- ----------- 
1           9
2           10
5           7
6           6
9           5
15          7
22          3
23          2
29          1
100         NULL
*/

[lovezgs]

有没有高效一点儿的语句啊，我的数据量很大。

[Limpire]

[Quote=引用 11 楼 lovezgs 的回复:]
有没有高效一点儿的语句啊，我的数据量很大。
[/Quote]

update 表 set num=num-1

[huangqing_80]

有点儿看不明白,向上移几行,数字是否是连续的,都会影响到语句的

[ivan_ren]

create table t(id int identity(1,1),num int)
insert into t(num)
select 10
union select 9
union select 8
union select 7
union select 6
union select 5
union select 4
union select 3
union select 2
union select 1
select id,case when num -1> 0 then num-1 else null end   as num from t
drop table t

[lovezgs]

我用的数据只是一个例子，实际数据中的数据是没有规律的。

[parss]

select id = (select count(1) from ta where num < = a.num), num　from table a

[lovezgs]

num中的数据的大小关系是不定的，前面的有可能大还有可能小。

[chenxin2835]

declare @tb table (id int,num int)
declare @tb2 table (id int identity(1,1),num int)
insert into @tb select 1,10
insert into @tb select 2,7
insert into @tb select 3,8
insert into @tb select 4,9
insert into @tb select 5,6

insert into @tb select 6,5
insert into @tb select 7,4
insert into @tb select 8,3
insert into @tb select 9,2
insert into @tb select 10,1

Insert into @tb2
select num-1 as num
from @tb
order by num desc

Update @tb
Set num=a.num
From @tb2 a,@tb b
where b.id=a.id

select * from @tb

[hui_hui_2007]

create table # (id int,num int)
insert into # values(1,10)
insert into # values(2,9)
insert into # values(3,8)
insert into # values(4,7)
insert into # values(5,6)
insert into # values(6,5)
insert into # values(7,4)
insert into # values(8,3)
insert into # values(9,2)
insert into # values(10,1)

select * from #

update a
set num=b.num
from # a 
left join # b 
	on a.id=b.id-1

select * from #

/*
id          num         
----------- ----------- 
          1           9 
          2           8 
          3           7 
          4           6 
          5           5 
          6           4 
          7           3 
          8           2 
          9           1 
         10 NULL

（所影响的行数为 10 行）
*/

[youngerch]

俺觉得库设计好了，没必要这么做
相较之下耗资源

[luofeng2006]

select id,num=(select num from tb where id=a.id+1) from tb a

[Johnson_YP]

create table ta(id int,num int)
insert into ta select 1,10
insert into ta select 2,9
insert into ta select 3,10
insert into ta select 4,7
insert into ta select 5,6
insert into ta select 6,5
insert into ta select 7,7
insert into ta select 8,3
insert into ta select 9,2
insert into ta select 10,1

--select a.*,b.* from ta a left outer join ta b on a.id = b.id - 1

update a set a.num=b.num from ta a left outer join ta b on a.id = b.id - 1

/*
id num
----------- -----------
1 9
2 10
3 7
4 6
5 5
6 7
7 3
8 2
9 1
10 NULL
*/