你可以使用
DENSE_RANK
来完成这个任务:
SELECT id, home, [datetime], player, resource
FROM (
SELECT id, home, [datetime], player, resource,
DENSE_RANK() OVER (ORDER BY [datetime] DESC) AS rnk
FROM mytable) AS t
WHERE t.rnk <= 2
DENSE_RANK
函数将值1分配给所有与最新日期值相关的记录,将值2分配给所有与次新日期值相关的记录。
输出:
id home datetime player resource
=================================================
7 13 2009-12-12 02:00:00.000 borat 600
1 10 2009-04-03 03:00:00.000 john 399
2 11 2009-04-03 03:00:00.000 juliet 244
5 12 2009-04-03 03:00:00.000 borat 555
在MySQL中,你可以使用变量来模拟
DENSE_RANK
:
SELECT id, `datetime`, player, resource
FROM (
SELECT id, `datetime`, player, resource,
@rn := IF(@dt = `datetime`, @rn,
IF(@dt := `datetime`, @rn + 1, @rn + 1)) AS rnk
FROM mytable
CROSS JOIN (SELECT @rn := 0, @dt := '1900-01-01') AS vars
ORDER BY `datetime` DESC) AS t
WHERE t.rnk <= 2
Demo here