1 xxx
2 yyy
3 ooo
4 zzz
8 ppp
9 zzz
数据表如上,
我想实现找到ID=4之前的ID为3和之后的ID为8的记录
用row_number()函数,请问怎么写,谢谢.
2 yyy
3 ooo
4 zzz
8 ppp
9 zzz
数据表如上,
我想实现找到ID=4之前的ID为3和之后的ID为8的记录
用row_number()函数,请问怎么写,谢谢.
解决方案 »
- 从数据库中查询的两条数据进行交换,在一个事务中怎样处理???
- 一条sql语句,求最大值,最小值,平均值,最新值
- php连接MSSQL
- 为什么我的SQL Server2000的master库里没有XP_COMMSHELL存储过程?
- 请问insert bulk和bulk insert的区别,给出一个具体用法实例,先谢了!
- 关于随机选取数据的解决方案--集思广益!
- 关于启动Microsoft SQL Server 2000服务器的问题?
- 如何通过行号获取选手排名?
- 关于执行*.sql文件的问题
- 请问:数据库创建好之后,我想创建与上一年一样结构的数据库,就要把上年的数据库的表,存储过程,触发器等全部导入新数据库,详细请进...
- 使用sql prompt时怎么刷新库结构?
- 我晕了,这是一本什么书啦??
AS
BEGIN
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM TB
DELCARE @IDD INT
SET @IDD=0
SELECT @IDD=IDD FROM #T WHERE ID=@ID
SELECT * FROM #T T
WHERE EXISTS(SELECT 1 FROM #T WHERE IDD=@IDD+1)AND
WHERE EXISTS(SELECT 1 FROM #T WHERE IDD=@IDD-1)END
AS
BEGIN
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM TB
DECLARE @IDD INT
SET @IDD=0
SELECT @IDD=IDD FROM #T WHERE ID=@ID
SELECT * FROM #T T
WHERE EXISTS(SELECT 1 FROM #T WHERE IDD=@IDD+1)AND
WHERE EXISTS(SELECT 1 FROM #T WHERE IDD=@IDD-1)END???试试?
select *,row_number() over (order by col1) as col3
from tb
) as t
where col3 between 3 and 5
AS
BEGIN
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM TB
DECLARE @IDD INT
SET @IDD=0
SELECT @IDD=IDD FROM #T WHERE ID=@ID
SELECT * FROM #T T
WHERE EXISTS(SELECT 1 FROM #T WHERE IDD=@IDD+1)
OR
EXISTS(SELECT 1 FROM #T WHERE IDD=@IDD-1)END
再修改下
@id int
as
begin
select rn=row_number() over (order by getdate()),* into # from tb
select col1,col2
from #
where rn=(select rn from # where col1=@id ) or rn=(select rn-1 from # where col1=@id )
end
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( col1 int, col2 varchar(10))
go
insert tb SELECT
1 , 'xxx' UNION ALL SELECT
2 , 'yyy' UNION ALL SELECT
3 , 'ooo' UNION ALL SELECT
4 , 'zzz' UNION ALL SELECT
8 , 'ppp' UNION ALL SELECT
9 , 'zzz'
go
create proc kp
@id int
as
begin
select rn=row_number() over (order by getdate()),* into # from tb
select col1,col2
from #
where rn=(select rn+1 from # where col1=@id ) or rn=(select rn-1 from # where col1=@id )
end
go
exec kp 4
go
col1 col2
----------- ----------
3 ooo
8 ppp
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( col1 int, col2 varchar(10))
go
insert tb SELECT
1 , 'xxx' UNION ALL SELECT
2 , 'yyy' UNION ALL SELECT
3 , 'ooo' UNION ALL SELECT
4 , 'zzz' UNION ALL SELECT
8 , 'ppp' UNION ALL SELECT
9 , 'zzz'
go
create proc kp
@id int
as
begin
select rn=row_number() over (order by getdate()),* into # from tb
select col1,col2
from #
where rn=(select rn+1 from # where col1=@id ) or rn=(select rn-1 from # where col1=@id )
end
go
exec kp 4
go
col1 col2
----------- ----------
3 ooo
8 ppp
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( col1 int, col2 varchar(10))
go
insert tb SELECT
1 , 'xxx' UNION ALL SELECT
2 , 'yyy' UNION ALL SELECT
3 , 'ooo' UNION ALL SELECT
4 , 'zzz' UNION ALL SELECT
8 , 'ppp' UNION ALL SELECT
9 , 'zzz'
go
create proc kp1
@id int
as
begin
declare @i int
set @i=(select COUNT(*) from tb where col1<=@id)
select col1,col2
from (select rn=row_number() over (order by getdate()),* from tb )k
where rn=@i+1 or rn=@i-1
end
go
exec kp1 4
go
/*
col1 col2
----------- ----------
3 ooo
8 ppp*/
Create table TB(id int,cName char(10))
insert into tb
SELECT 1,'xxx' UNION ALL
SELECT 2,'yyy' UNION ALL
SELECT 3,'ooo' UNION ALL
SELECT 4,'zzz' UNION ALL
SELECT 8,'ppp' UNION ALL
SELECT 9,'zzz'DECLARE @ID INT
SET @ID=4SELECT * FROM
(SELECT TOP 3 * FROM
(SELECT *,Row_Number() over(order by id) as RowN FROM TB) A
ORDER BY ABS(RowN-@id))B
ORDER BY ID/*
---------------------
id cName RowN
3 ooo 3
4 zzz 4
8 ppp 5(3 行受影响)*/
1 xxx
2 yyy
3 ooo
4 zzz
8 ppp
9 zzz
2 yyy
3 ooo <-- 前一篇文章ID为3
4 zzz <--当前文章页ID为4
8 ppp <----下一篇文章页ID为8
9 zzz
--我的意思是,比如当前文章页面的ID是4,我想找前一篇文章和下一篇文章的ID号
请用ROW_NUMBER OVER()在存储过程里求出前一篇文章和下一篇文章的ID号,可被前台调用.
DROP TABLE TB
Create table TB(id int,cName char(10))
insert into tb
SELECT 1,'xxx' UNION ALL
SELECT 2,'yyy' UNION ALL
SELECT 3,'ooo' UNION ALL
SELECT 4,'zzz' UNION ALL
SELECT 8,'ppp' UNION ALL
SELECT 9,'zzz'DECLARE @ID INT
SET @ID=4
SELECT * FROM (SELECT TOP 1 * FROM TB WHERE ID<@ID ORDER BY ID DESC) T
UNION ALL
SELECT * FROM (SELECT TOP 1 * FROM TB WHERE ID>@ID ORDER BY ID ASC) T/*
3 ooo
8 ppp
*/
9 不存在下一篇
用11楼的方法 ROW_NUMBEROVER()在存储过程里
。不然别人早写其他的方法啦。呵呵
1 , 'xxx' from dual UNION ALL SELECT
2 , 'yyy' from dual UNION ALL SELECT
3 , 'ooo' from dual UNION ALL SELECT
4 , 'zzz' from dual UNION ALL SELECT
8 , 'ppp' from dual UNION ALL SELECT
9 , 'zzz' from dual ;select b.* from
(
select rownum seq, tb.* from tb
) b,
(
select seq from
(
select rownum seq, tb.* from tb
) a
where a.col1=3 --更改此值,sql server 可更改下语句,差不多
) a
where b.seq=a.seq+1 or b.seq=a.seq-1
where col1=(SELECT max(col1) FROM TB a WHERE col1<4 )
or col1=(SELECT max(col1) FROM TB a WHERE col1>4 )