我的数据库是这样的:nInfoMain表
select * from nInfoMain order by TopFlag desc,IssueTime desc
ID text TopFlag time
280 内容 1 2011-04-07 11:03:05
164 内容 1 2011-03-07 11:03:05
583 内容 0 2011-11-05 11:03:05
251 内容 0 2011-06-11 11:03:05
283 内容 0 2011-04-05 11:03:05
211 内容 0 2011-03-24 11:03:05实际就是顺序先按TopFlag=1(被置顶的内容)时间倒序排在前面,然后再按没有置顶的内容时间倒序排在后面这样如果,已经知道本条数据是其中一条,如何查出他的上一条数据和下一条。因为加入了TopFlag我就被搞昏了自己写了个
select top 1 * from [nInfoMain]
where time<(select time from [nInfoMain]
where ID=251) and ID<>251
不正确
select * from nInfoMain order by TopFlag desc,IssueTime desc
ID text TopFlag time
280 内容 1 2011-04-07 11:03:05
164 内容 1 2011-03-07 11:03:05
583 内容 0 2011-11-05 11:03:05
251 内容 0 2011-06-11 11:03:05
283 内容 0 2011-04-05 11:03:05
211 内容 0 2011-03-24 11:03:05实际就是顺序先按TopFlag=1(被置顶的内容)时间倒序排在前面,然后再按没有置顶的内容时间倒序排在后面这样如果,已经知道本条数据是其中一条,如何查出他的上一条数据和下一条。因为加入了TopFlag我就被搞昏了自己写了个
select top 1 * from [nInfoMain]
where time<(select time from [nInfoMain]
where ID=251) and ID<>251
不正确
declare @rid intselect *,rid=identity(int,1,1)
into #tb
from nInfoMain
order by TopFlag desc,IssueTime descselect @rid = rid from #tb where --你查询哪条数据的条件select *
from #tb
where rid between @rid-1 and @rid+1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-24 11:36:15
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[text] varchar(4),[TopFlag] int,[time] datetime)
insert [tb]
select 280,'内容',1,'2011-04-07 11:03:05' union all
select 164,'内容',1,'2011-03-07 11:03:05' union all
select 583,'内容',0,'2011-11-05 11:03:05' union all
select 251,'内容',0,'2011-06-11 11:03:05' union all
select 283,'内容',0,'2011-04-05 11:03:05' union all
select 211,'内容',0,'2011-03-24 11:03:05'
--------------开始查询--------------------------
declare @id int
set @id=164
;with f as
(
select px=ROW_NUMBER()over(order by getdate()),* from tb
)
select ID,TEXT,topflag,time from f where px=(select px from f where ID=@id)-1
union all
select ID,TEXT,topflag,time from f where px=(select px from f where ID=@id)+1
----------------结果----------------------------
/* ID TEXT topflag time
----------- ---- ----------- -----------------------
280 内容 1 2011-04-07 11:03:05.000
583 内容 0 2011-11-05 11:03:05.000(2 行受影响)*/
insert into nInfoMain select 280,'内容',1,'2011-04-07 11:03:05'
insert into nInfoMain select 164,'内容',1,'2011-03-07 11:03:05'
insert into nInfoMain select 583,'内容',0,'2011-11-05 11:03:05'
insert into nInfoMain select 251,'内容',0,'2011-06-11 11:03:05'
insert into nInfoMain select 283,'内容',0,'2011-04-05 11:03:05'
insert into nInfoMain select 211,'内容',0,'2011-03-24 11:03:05'
go
;with cte as(
select *,rn=row_number()over(order by TopFlag desc,IssueTime desc) from nInfoMain
)select ID,text,TopFlag,IssueTime from cte a
where abs(rn-(select rn from cte where id=251))=1
/*
ID text TopFlag IssueTime
----------- ---------- ----------- -----------------------
583 内容 0 2011-11-05 11:03:05.000
283 内容 0 2011-04-05 11:03:05.000(2 行受影响)*/
go
drop table nInfoMain
if object_id('nInfoMain','U') is not null
drop table nInfoMain
go
create table nInfoMain
(
id int,
text1 varchar(10),
topflag int,
time datetime
)
go
insert into nInfoMain
select 280,'内容',1,'2011-04-07 11:03:05' union all
select 164,'内容',1,'2011-03-07 11:03:05' union all
select 583,'内容',0,'2011-11-05 11:03:05' union all
select 251,'内容',0,'2011-06-11 11:03:05' union all
select 283,'内容',0,'2011-04-05 11:03:05' union all
select 211,'内容',0,'2011-03-24 11:03:05'
go
select *,row=row_number() over(order by topflag desc,time desc) from nInfoMain order by topflag desc,time desc
/*
id text1 topflag time row
----------- ---------- ----------- ----------------------- --------------------
280 内容 1 2011-04-07 11:03:05.000 1
164 内容 1 2011-03-07 11:03:05.000 2
583 内容 0 2011-11-05 11:03:05.000 3
251 内容 0 2011-06-11 11:03:05.000 4
283 内容 0 2011-04-05 11:03:05.000 5
211 内容 0 2011-03-24 11:03:05.000 6(6 行受影响)
*/排序后加入一个row序号列,根据序号获取前一条和后一台数据
第 1 行: ';' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 3
'ROW_NUMBER' 不是可以识别的 函数名。
服务器: 消息 137,级别 15,状态 1,行 5
必须声明变量 '@id'。
服务器: 消息 137,级别 15,状态 1,行 7
必须声明变量 '@id'。