虾米求sql语句

lovezgs 2008-03-20 09:46:22
假设我有一个表,里面有两个字段,为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

语句要高效
...全文
109 点赞 收藏 22
写回复
22 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Johnson_YP 2008-03-24
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
*/
回复
luofeng2006 2008-03-24
select id,num=(select num from tb where id=a.id+1) from tb a
回复
youngerch 2008-03-24
俺觉得库设计好了,没必要这么做
相较之下耗资源
回复
hui_hui_2007 2008-03-24

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 行)
*/
回复
chenxin2835 2008-03-24
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
回复
lovezgs 2008-03-24
num中的数据的大小关系是不定的,前面的有可能大还有可能小。
回复
parss 2008-03-24
select id = (select count(1) from ta where num < = a.num), num from table a
回复
lovezgs 2008-03-24
我用的数据只是一个例子,实际数据中的数据是没有规律的。
回复
ivan_ren 2008-03-24
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
回复
huangqing_80 2008-03-24
有点儿看不明白,向上移几行,数字是否是连续的,都会影响到语句的
回复
Limpire 2008-03-23
[Quote=引用 11 楼 lovezgs 的回复:]
有没有高效一点儿的语句啊,我的数据量很大。
[/Quote]

update 表 set num=num-1
回复
lovezgs 2008-03-23
有没有高效一点儿的语句啊,我的数据量很大。
回复
-晴天 2008-03-20
如果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
*/
回复
-晴天 2008-03-20
[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

其他几位大叔做得都不对.
回复
cxmcxm 2008-03-20
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
回复
cxmcxm 2008-03-20
--假设表名为t ,id号必须连续
update a
set num=b.num
from t a,t b where a.id=b.id-1
回复
-狙击手- 2008-03-20
select id,case num when 1 then '' else ltrim(num - 1) end as num
from ta
回复
-狙击手- 2008-03-20

晕,看错了, 以为排序 呢




回复
wzy_love_sly 2008-03-20
1	9
2 8
3 7
4 6
5 5
6 4
7 3
8 2
9 1
10
回复
-狙击手- 2008-03-20
select id = (select count(1) from ta where num > = a.num),
num
from ta a
order by id
回复
加载更多回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-20 09:46
社区公告
暂无公告