＋设为首页 ＋添加到收藏夹
现在有这样的记录
ID RECORD_TIME
1 2005-02-28 18:00:00.000
2 2005-05-30 11:26:36.000
2 2005-04-22 11:09:29.000
2 2005-02-28 18:00:00.000
3 2005-02-28 18:00:00.000
4 2005-02-28 18:00:00.000
5 2005-02-28 18:00:00.000
6 2005-02-28 18:00:00.000
7 2005-02-28 18:00:00.000
8 2005-02-28 18:00:00.000
9 2005-02-28 18:00:00.000
10 2005-02-28 18:00:00.000
11 2005-05-22 09:19:17.000
11 2005-05-04 14:02:37.000
11 2005-02-28 18:00:00.000
现在想取离2005-6-1最近的每个ID的记录
例如ID为2的记录应该取
2 2005-05-30 11:26:36.000
2 2005-04-22 11:09:29.000
这样的sql语句应该怎么写？
离2005-6-1最近?
总该有个时间范围
select * from tbale where RECORD_TIME <= 2005-04-22 order by RECORD_TIME desc
想不出来，帮不上忙。。
select * from tbale where RECORD_TIME <= 2005-04-22
and rownum <=2 order by RECORD_TIME desc 离20050402最近的两条！！
rownum 是ORACLE数据库表的一个伪列，用来返回行号的
不过jia20003(gloomy fish)写的有问题，应该是
select * from(select * from tbale where RECORD_TIME <= 2005-04-22
order by RECORD_TIME desc) where rownum < 2
TRY:
SELECT *
FROM (SELECT TOP 10000 id, RECORD_TIME, CASE WHEN DATEDIFF(n, RECORD_TIME,
2005-6-1) > 0 THEN DATEDIFF(n, RECORD_TIME, 2005-6-1)
ELSE - DATEDIFF(n, RECORD_TIME, 2005-6-1) END AS f
FROM temptable
ORDER BY id, f) c
WHERE f IN
(SELECT TOP 2 f
FROM (SELECT TOP 10000 id, RECORD_TIME, CASE WHEN DATEDIFF(n,
RECORD_TIME, 2005-6-1) > 0 THEN DATEDIFF(n, RECORD_TIME,
2005-6-1) ELSE - DATEDIFF(n, RECORD_TIME, 2005-6-1)
END AS f
FROM temptable
ORDER BY id, f) t
WHERE c.id = t .id)
