虾米求sql语句

2008-03-20 09:46:22 176 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Windows+VSCode+Cline+MCP全流程配置指南本文提供了一份详细的Windows+VSCode+Cline+MCP全流程配置指南，旨在帮助开发者打造高效的AI辅助编码工作站。文章从环境准备讲起，逐步指导安装VSCode、配置Cline插件、搭建Node.js环境，并重点详解如何通过MCP为Cline集成文件系统和GitHub等工具服务器，实现用自然语言操控开发环境，从而显著提升日常编码与文件管理效率。阅读详情

假设我有一个表，里面有两个字段，为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

语句要高效

...全文

前端监控性能指标文章目录前端监控性能指标性能指标阶段性指标关键性能指标小程序W3C Level 1兼容性常规用法注意事项1、计算HTML文档请求使用 Nav Timing2、计算静态资源使用 getEntriesByType('resource') 代替 getEntries()3、secureConnectionStart 问题4、跨域资源设置响应头 Timing-Allow-Origin5、注意属性值为 0 ... 阅读详情

lovezgs

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

-狙击手- 2008-03-20

打赏
举报

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

       num

from ta a

order by id

加载更多回复（2）

虾米求sql语句

相关推荐