库中数据如下:
ID | DATE | CODE |
---------------------------------
2 |2012-01-01 |P120101-01|
1 |2012-01-02 |P120102-01|
3 |2012-01-02 |P120102-02|
7 |2012-01-02 |P120102-03|
5 |2012-01-03 |P120103-01|
6 |2012-01-07 |P120107-01|
4 |2012-01-07 |P120107-02|
8 |2012-01-07 |P120107-03|
11 |2012-01-09 |P120109-01|
10 |2012-01-09 |P120109-02|
9 |2012-01-09 |P120109-03|数据以 DATE 和 CODE 进行排序.我已知ID = 4 这条记录的值,我希望查询到第 id=6 这条记录的数据。我已知ID = 4 这条记录的值,我希望查询到第 id=8 这条记录的数据。
ID | DATE | CODE |
---------------------------------
2 |2012-01-01 |P120101-01|
1 |2012-01-02 |P120102-01|
3 |2012-01-02 |P120102-02|
7 |2012-01-02 |P120102-03|
5 |2012-01-03 |P120103-01|
6 |2012-01-07 |P120107-01|
4 |2012-01-07 |P120107-02|
8 |2012-01-07 |P120107-03|
11 |2012-01-09 |P120109-01|
10 |2012-01-09 |P120109-02|
9 |2012-01-09 |P120109-03|数据以 DATE 和 CODE 进行排序.我已知ID = 4 这条记录的值,我希望查询到第 id=6 这条记录的数据。我已知ID = 4 这条记录的值,我希望查询到第 id=8 这条记录的数据。
select @date='2012-01-07',@code='|P120107-02|'
;with t as
(
select row_number() over(order by DATE,CODE) rn,DATE,CODE from [你的表]
)
select * from t a
where exists (select 1 from t b where b.DATE=@date and b.CODE=@code and (a.rn=b.rn-1 or a.rn=b.rn+1))
--没看明白,你的ID=4得到6,8,什么逻辑得到的,依据是什么,这样?
select t1.* from 你的表 t,你的表 t1
where t.DATE=t1.DATE and t.ID=4 and t1.ID<>4
order by [DATE],[CODE];
select top 1 * from tab
where date>='2012-01-07' and code>='P120107-02' and id<>4
order by date,code asc
go
create table test(ID int,DATE varchar(10),CODE varchar(10))
go
insert into test
select 2 ,'2012-01-01' ,'P120101-01' union all
select 1 ,'2012-01-02' ,'P120102-01' union all
select 3 ,'2012-01-02' ,'P120102-02' union all
select 7 ,'2012-01-02' ,'P120102-03' union all
select 5 ,'2012-01-03' ,'P120103-01' union all
select 6 ,'2012-01-07' ,'P120107-01' union all
select 4 ,'2012-01-07' ,'P120107-02' union all
select 8 ,'2012-01-07' ,'P120107-03' union all
select 11 ,'2012-01-09' ,'P120109-01' union all
select 10 ,'2012-01-09' ,'P120109-02' union all
select 9 ,'2012-01-09' ,'P120109-03'
go
declare @date varchar(10),@code varchar(10)
select @date='2012-01-07',@code='P120107-02'
;with t as
(
select row_number() over(order by DATE,CODE) rn,DATE,CODE from test
)
select * from t a
where exists (select 1 from t b where b.DATE=@date and b.CODE=@code and (a.rn=b.rn-1 or a.rn=b.rn+1))/*(11 行受影响)
rn DATE CODE
-------------------- ---------- ----------
6 2012-01-07 P120107-01
8 2012-01-07 P120107-03(2 行受影响)
*/
---
如果:
select * from tab
where date='2012-01-07' and code>='P120107-02' order by code
为空则
select min(code) from tab
where date =‘2012-01-09’ order by code
的第一条。问题是:1、如何取得下一条记录的日期‘2012-01-09’
2、如何并成一条记录
3、数据库不认 TOP 1
是啊,可是每次查询都是返回一个由 id 形成的list,记录多的话,占网络啊
--意思是查询4邻近的两个数据吧
declare table @tab(ID int,DATE varchar(10),CODE varchar(10))
insert into @tab
select 2 ,'2012-01-01' ,'P120101-01' union all
select 1 ,'2012-01-02' ,'P120102-01' union all
select 3 ,'2012-01-02' ,'P120102-02' union all
select 7 ,'2012-01-02' ,'P120102-03' union all
select 5 ,'2012-01-03' ,'P120103-01' union all
select 6 ,'2012-01-07' ,'P120107-01' union all
select 4 ,'2012-01-07' ,'P120107-02' union all
select 8 ,'2012-01-07' ,'P120107-03' union all
select 11 ,'2012-01-09' ,'P120109-01' union all
select 10 ,'2012-01-09' ,'P120109-02' union all
select 9 ,'2012-01-09' ,'P120109-03'
go
declare @date varchar(10),@code varchar(10)
select @date='2012-01-07',@code='P120107-02'
;with cte as
(
select row_number() over(order by DATE,CODE) rn,DATE,CODE from @tab
)
select * from cte a
where exists
(select 1 from cte b
where b.DATE=@date and
b.CODE=@code and
(a.rn=b.rn-1 or
a.rn=b.rn+1))
declare @id int
select @id=(select id from tb where date='2012-01-07' and CODE ='P120107-02')
;with t
as(
select px=row_number()over(order by DATE,CODE asc),* from tb
)
select * from t
where px between (select px from t where id=@id)-1 and (select px from t where id=@id)+1--不晓得你是不是这个意思
应该是按照date升降序排序。
下一条:
select top 1 from tb
where id > 4
order by date asc上一条:
select top 1 from tb
where id < 4
order by date desc