求一sql语句,请各位高手进来帮忙
比如数据库中有这样的id字段,且是主键,以下为删除记录之后的id的编号:
1
3
4
9
21
35
38
想问一下如何得到当前记录的上一条和下一条记录呢?
比如当前记录是9如何通过sql语句得到下一条(21)上一条(4)呢.?谢谢帮忙,虽然这个问题有点刺手,还
是请大家帮我一下,谢谢了
[解决办法]
ms sql:
select top 1 id as preid from table a where a.id < 9 order by a.id desc
union all
select top 1 id as nextid from table a where a.id > 9 order by a.id
oracle:
select id as preid from table a where a.id < 9 and rownum = 1 order by a.id desc
union all
select id as nextid from table a where a.id > 9 and rownum = 1 order by a.id
[解决办法]
select top 1 * from article where insro= 'pro ' and id <9 order by ID desc
select top 1 * from article where insro= 'pro ' and id> 9 order by ID
[解决办法]
IDENTITY(int, 1, 1) 数据类型int，从1开始，步长为1递增。
本函数只可与SELECT INTO 表 一起用。
select identity(int,1,1) as id,* into table2 from table1
在table2中加一列ID从1开始，2.3.4......N
