按列订购并对列进行限制

Question

我有一个具有以下列的表vehicles

id  license_number created_at                   updated_at                   pump_number
1   ABC123         2014-10-28 13:43:58.679812   2014-10-28 13:43:58.679812   1
2   XYZ224         2014-10-29 05:24:18.163042   2014-10-29 05:24:18.163042   2
3   AB1111         2014-10-29 06:50:56.891475   2014-10-29 06:50:56.891475   1
4   AB1112         2014-11-10 06:20:06.666361   2014-11-10 06:20:06.666361   1
5   ABC123         2014-11-10 06:21:10.160651   2014-11-10 06:21:10.160651   1
6   XXU111         2014-11-10 06:33:57.813795   2014-11-10 06:33:57.813795   2
7   ABC323         2014-11-11 09:02:48.509402   2014-11-11 09:02:48.509402   1
8   YYY123         2014-11-12 06:12:13.851119   2014-11-12 06:12:13.851119   2
9   ZZZ123         2014-11-12 06:12:42.099546   2014-11-12 06:12:42.099546   2

其中id是主键，并且是自动登录的。

我想获取表中每一个updated_at的两个最近的pump_number记录

所以查询应该只返回ID为9,8,7,5的行

Answer 1

我发现了类似的问题，here。检查一下，以供参考。

这应该适用于你：

SELECT *
FROM   vehicles t1
WHERE  (SELECT Count(*)
        FROM   vehicles t2
        WHERE  t1.pump_number = t2.pump_number
               AND t1.updated_at < t2.updated_at) < 2 

Answer 2

下面的查询将返回每个泵号的最后2条记录：

select * from (select * from vehicles  order by updated_at desc)a group by pump_number 
Union 
select * from (select * from vehicles  order by updated_at desc where id not in 
(select id from (select * from vehicles  order by updated_at desc) group by pump_number)b
)c group by pump_number 

Answer 3

select id,pump_number from (
select id,pump_number,row_number() over(partition by pump_number order by updated_at 
desc  ) rn from tempd ) 
where rn<=2
order by 1;

您可以使用分析函数来尝试，就像我在oracle sql中所做的那样。