表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)在mssql已开了一贴,mssql有人写出来了,但mysql的,还是不对,来这边再问一次吧。
原贴:http://topic.csdn.net/u/20111226/14/f8928731-4951-45e2-8e13-c563bdc095e1.html?seed=1234716279&r=77079730#r_77079730
现已知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)在mssql已开了一贴,mssql有人写出来了,但mysql的,还是不对,来这边再问一次吧。
原贴:http://topic.csdn.net/u/20111226/14/f8928731-4951-45e2-8e13-c563bdc095e1.html?seed=1234716279&r=77079730#r_77079730
from test A,test B
where B.id=4 and A.timedate<=B.timedate
order by A.timedate desc,A.id desc
limit 1,1
create table tmp_t
select @iid:=@iid+1 as iid, xxx.*
from Test
order by timedate desc, id desc;select t2.id, t2.timedate
from tmp_t as t1
left join tmp_t as t2 on t1.iid = t2.iid - 1
where t1.id = [指定的ID];-- 执行 drop table if exists tmp_t;
mysql> select * from test;
+------+---------------------+
| id | timedate |
+------+---------------------+
| 1 | 2011-07-11 00:00:00 |
| 2 | 2011-06-11 00:00:00 |
| 3 | 2011-06-11 00:00:00 |
| 4 | 2011-06-11 00:00:00 |
| 5 | 2011-09-11 00:00:00 |
| 6 | 2011-05-11 00:00:00 |
+------+---------------------+
6 rows in set (0.00 sec)mysql> select * from test
-> where timedate<(select timedate from Test where id=4)
-> or (timedate=(select timedate from Test where id=4) and id <4)
-> order by timedate desc,id desc
-> limit 1;
+------+---------------------+
| id | timedate |
+------+---------------------+
| 3 | 2011-06-11 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)mysql> select * from test
-> where timedate<(select timedate from Test where id=3)
-> or (timedate=(select timedate from Test where id=3) and id <3)
-> order by timedate desc,id desc
-> limit 1;
+------+---------------------+
| id | timedate |
+------+---------------------+
| 2 | 2011-06-11 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)mysql> select * from test
-> where timedate<(select timedate from Test where id=2)
-> or (timedate=(select timedate from Test where id=2) and id <2)
-> order by timedate desc,id desc
-> limit 1;
+------+---------------------+
| id | timedate |
+------+---------------------+
| 6 | 2011-05-11 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)mysql>