为没有子查询的已知其他几列选择具有 MAX(column) 的一行

2022-01-23 00:00:00 sql subquery mysql

我的表格包含用户对不同项目的投票.它有以下字段:

My table contains votes of users for different items. It has the following fields:

id, user_id, item_id, vote, utc_time

我了解如何为 #item# 获得 #user# 的最后一票，但它使用子查询:

I understand how to get the last vote of #user# for #item#, but it uses subquery:

    SELECT votes.*, items.name, items.price
        FROM votes JOIN items ON items.id = votes.item_id
        WHERE user_id = #user# AND item_id = #item#
            AND utc_time = (
                SELECT MAX(utc_time) FROM votes
                WHERE user_id = #user# AND item_id = #item#
            )

它有效，但对我来说看起来很愚蠢......应该有一种更优雅的方式来获得这个记录.我尝试了这里建议的方法，但我还不能让它工作，所以我会感谢你的帮助:如何在 SQL 中选择具有 MAX(列值)、DISTINCT 的行?

It works, but it looks quite stupid to me... There should be a more elegant way to get this one record. I tried the approach suggested here, but I cannot make it work yet, so I'll appreciate your help: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

这个问题还有第二部分:Count rows with DISTINCT(几列)和MAX(另一列)

There is a second part to this question: Count rows with DISTINCT(several columns) and MAX(another column)

推荐答案

您只需要结果中的一行，即具有 MAX(utc_time) 的行.在 MySQL 中，有一个 LIMIT 子句，您可以使用 ORDER BY:

You want just one row from the result, the one with MAX(utc_time). In MySQL, there is a LIMIT clause you can apply with ORDER BY:

SELECT votes.*, items.name, items.price
    FROM votes JOIN items ON items.id = votes.item_id
    WHERE user_id = #user# AND item_id = #item#
    ORDER BY votes.utc_time DESC
        LIMIT 1 ;

(user_id, item_id, utc_time) 或 (item_id, user_id, utc_time) 上的索引会提高效率.

An index on either (user_id, item_id, utc_time) or (item_id, user_id, utc_time) will be good for efficiency.

相关文章