表Test结构为两个字段,id与timedate,id为自动增长,timedate为时间,id唯一,时间,有可能会相同,也有可能为空。
现已知id值,我要通过id查询一条记录,查询规则为按时间降序,id降序的下一条记录,
select * from Test order by timedate desc,id desc 即按条语句查询结果,找出该id所在记录的下一条记录。
测试数据如下:
id timedate
1 2011-7-11 0:00:00
2 2011-6-11 0:00:00
3 2011-6-11 0:00:00
4 2011-6-11 0:00:00
5 2011-9-11 0:00:00
6 2011-5-11 0:00:00通过select * from Test order by timedate desc,id desc 排序后:
id timedate
5 2011-9-11 0:00:00
1 2011-7-11 0:00:00
4 2011-6-11 0:00:00
3 2011-6-11 0:00:00
2 2011-6-11 0:00:00
6 2011-5-11 0:00:00
现在我想实现的是这样的:
如果id=4,那么,我想查到id=3这条记录,
如果id=3,那么,我想查到id=2这条记录,
如果id=2,那么,我想查到id=6这条记录该怎么写呢?下面这样写不行:select * from Test where id = 4 and timedate <= (select timedate from Test where id=4)这个看起来简单,没想到,试了半天没试出来,请各位大虾帮忙了,先谢了。
现已知id值,我要通过id查询一条记录,查询规则为按时间降序,id降序的下一条记录,
select * from Test order by timedate desc,id desc 即按条语句查询结果,找出该id所在记录的下一条记录。
测试数据如下:
id timedate
1 2011-7-11 0:00:00
2 2011-6-11 0:00:00
3 2011-6-11 0:00:00
4 2011-6-11 0:00:00
5 2011-9-11 0:00:00
6 2011-5-11 0:00:00通过select * from Test order by timedate desc,id desc 排序后:
id timedate
5 2011-9-11 0:00:00
1 2011-7-11 0:00:00
4 2011-6-11 0:00:00
3 2011-6-11 0:00:00
2 2011-6-11 0:00:00
6 2011-5-11 0:00:00
现在我想实现的是这样的:
如果id=4,那么,我想查到id=3这条记录,
如果id=3,那么,我想查到id=2这条记录,
如果id=2,那么,我想查到id=6这条记录该怎么写呢?下面这样写不行:select * from Test where id = 4 and timedate <= (select timedate from Test where id=4)这个看起来简单,没想到,试了半天没试出来,请各位大虾帮忙了,先谢了。
解决方案 »
- 请问64位sql2008下用xp_cmdshell调用一个java程序报错,该怎么解决
- 请问在什么情况下使用游标?请说一说游标的基本使用语法??谢谢!!!!
- 未能为视图或函数解析分配辅助表。超过了查询中表的最大数目256??请各位高手关注!!
- 请问compute子句,谢谢,急!
- sql语句问题
- 如何用程序实现建立一个以一个备份文件为内容的数据库,急,谢谢!
- 简单问题!这样一条SQL语句怎么写?
- 求一簡單SQL語句,謝謝大家
- 各位GG,JJ,DD,MM,你们准备如何虚度五一大好时光呀?
- SQL server 2000+SQL server2000 JDBC Driver+Java数据库连接的种种困扰,高分求救
- 用powerdesigner生成指定的数据库
- sqlserver2008 inset出错:无法打开与 SQL Server 的连接 [53]. 求高手解答
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-26 14:53:26
-- 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,[timedate] datetime)
insert [tb]
select 1,'2011-7-11 0:00:00' union all
select 2,'2011-6-11 0:00:00' union all
select 3,'2011-6-11 0:00:00' union all
select 4,'2011-6-11 0:00:00' union all
select 5,'2011-9-11 0:00:00' union all
select 6,'2011-5-11 0:00:00'
--------------开始查询--------------------------
;with f as
(
select px=ROW_NUMBER()over(order by timedate desc,id desc),* from tb
)select id,timedate from f where px=(select px+1 from f where ID=3)----------------结果----------------------------
/*id timedate
----------- -----------------------
2 2011-06-11 00:00:00.000(1 行受影响)
*/
我两个数据库都有用到
into #t
from tab
order by timedate desc,id descselect * from #t where rownum=
(select rownum+1 from #t where id=[指定的id])drop table #t
insert into tb select 1,'2011-7-11 0:00:00'
insert into tb select 2,'2011-6-11 0:00:00'
insert into tb select 3,'2011-6-11 0:00:00'
insert into tb select 4,'2011-6-11 0:00:00'
insert into tb select 5,'2011-9-11 0:00:00'
insert into tb select 6,'2011-5-11 0:00:00'
go
declare @id int
set @id=2
select top 1 *
from tb a
where timedate<(select timedate from tb where id=@id)
or id<(select top 1 id from tb where timedate=a.timedate and id<=@id order by id desc)
order by timedate desc,id desc
/*
id timedate
----------- -----------------------
6 2011-05-11 00:00:00.000(1 行受影响)*/
go
drop table tb
insert into tb select 1,'2011-7-11 0:00:00'
insert into tb select 2,'2011-6-11 0:00:00'
insert into tb select 3,'2011-6-11 0:00:00'
insert into tb select 4,'2011-6-11 0:00:00'
insert into tb select 5,'2011-9-11 0:00:00'
insert into tb select 6,'2011-5-11 0:00:00'
go
declare @id int
set @id=4
select top 1 *
from tb a
where timedate<(select timedate from tb where id=@id)
or id<(select top 1 id from tb where timedate=a.timedate and id<=@id order by id desc)
order by timedate desc,id desc
/*
id timedate
----------- -----------------------
3 2011-06-11 00:00:00.000(1 行受影响)
*/
go
drop table tb
insert into tb select 1,'2011-7-11 0:00:00';
insert into tb select 2,'2011-6-11 0:00:00';
insert into tb select 3,'2011-6-11 0:00:00';
insert into tb select 4,'2011-6-11 0:00:00';
insert into tb select 5,'2011-9-11 0:00:00';
insert into tb select 6,'2011-5-11 0:00:00';
go
;WITH cte_1 AS (
SELECT ROW_NUMBER() OVER (ORDER BY timedate DESC,id DESC) AS rn,id
FROM dbo.tb)
SELECT * FROM cte_1 AS A WHERE rn=(SELECT rn+1 FROM cte_1 AS B WHERE id=3)