这个mysql查询语句如何写

这个mysql查询语句怎么写？
表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

[解决办法]

SQL code

select A.* from test A,test Bwhere B.id=4 and A.timedate<=B.timedateorder by A.timedate desc,A.id desc limit 1,1
[解决办法]
你要物理顺序，插入有自增字段的临时表 OR 用变量解决
[解决办法]
SQL code
set @iid := 0;drop table if exists tmp_t;-- 如果使用临时表则不能自连接，可以创建两个临时表来处理，或是如此示例使用物理表，最后删除“临时”用的“物理”表create table tmp_tselect @iid:=@iid+1 as iid, xxx.*from Testorder by timedate desc, id desc;select t2.id, t2.timedatefrom tmp_t as t1 left join tmp_t as t2 on t1.iid = t2.iid - 1where t1.id = [指定的ID];-- 执行 drop table if exists tmp_t;
[解决办法]
SQL code
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>