mysql:按ID分组，每个ID获得最高优先级 [英] mysql: group by ID, get highest priority per each ID

问题描述

我有以下名为"pics"的mysql表，其中包含以下字段和示例数据:

I have the following mysql table called "pics", with the following fields and sample data:

id   vehicle_id    filename    priority
1    45            a.jpg       4
2    45            b.jpg       1
3    56            f.jpg       4
4    67            cc.jpg      4
5    45            kt.jpg      3
6    67            gg.jpg      1

是否可以在单个查询中为每个vehicle_id获取一行，并且该行具有最高优先级?

Is it possible, in a single query, to get one row for each vehicle_id, and the row be the highest priority?

我正在寻找的结果:

array (
  [0] => array( [id] => '2', [vehicle_id] => '45', [filename] => 'b.jpg',  [priority] => '1' ),
  [1] => array( [id] => '3', [vehicle_id] => '56', [filename] => 'f.jpg',  [priority] => '4' ),
  [2] => array( [id] => '6', [vehicle_id] => '67', [filename] => 'gg.jpg', [priority] => '1' )
);

如果不可能在单个查询中，最好的方法是什么?

If not possible in a single query, what would be the best approach?

谢谢！

推荐答案

虽然这可能是已接受"的答案，但

While this may be the 'accepted' answer, the performance of Mark's solution is under normal circumstances many times better, and equally valid for the question, so by all means, go for his solution in production!

SELECT a.id, a.vehicle_id, a.filename, a.priority
FROM pics a
LEFT JOIN pics b               -- JOIN for priority
ON b.vehicle_id = a.vehicle_id 
AND b.priority > a.priority
LEFT JOIN pics c               -- JOIN for priority ties
ON c.vehicle_id = a.vehicle_id 
AND c.priority = a.priority 
AND c.id < a.id
WHERE b.id IS NULL AND c.id IS NULL

假设"id"是不可为空的列.

Assuming 'id' is a non-nullable column.

[edit]:我不好，需要第二次加入，不能只用一次.

[edit]: my bad, need second join, cannot do it with just one.

这篇关于mysql:按ID分组，每个ID获得最高优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！