MySQL：通过子查询获取每个分类组的最新Name数据求助

Hey there! Subqueries can feel a bit opaque when you're first working with them, but let's walk through this problem step by step—you'll get the hang of it in no time.

First, let's assume your Table has columns that let us determine "latest" (like a timestamp column created_at or an auto-incrementing id). I'll use created_at as the example for "most recent," but you can swap it out for whatever field defines "latest" in your data.

Method 1: Join with a Subquery (Best for Performance) #

This approach first calculates the latest timestamp for each category, then matches those timestamps back to the original table to get the corresponding name.

SELECT t.category, t.name
FROM `Table` t
INNER JOIN (
    -- Subquery: Get the latest timestamp for each category
    SELECT category, MAX(created_at) AS latest_timestamp
    FROM `Table`
    GROUP BY category
) category_latest 
    ON t.category = category_latest.category 
    AND t.created_at = category_latest.latest_timestamp;

Breakdown:

• The subquery (category_latest) groups rows by category and uses MAX(created_at) to find the most recent timestamp for each group. This runs once, so it's efficient even on larger tables.
• We then join the original table (t) to this subquery, matching both category and created_at—this pulls only the rows from the original table that are the latest for their category.

If you use an auto-incrementing id to determine "latest" (since higher IDs mean newer rows), just swap MAX(created_at) for MAX(id):

SELECT t.category, t.name
FROM `Table` t
INNER JOIN (
    SELECT category, MAX(id) AS latest_id
    FROM `Table`
    GROUP BY category
) category_latest 
    ON t.category = category_latest.category 
    AND t.id = category_latest.latest_id;

Method 2: Correlated Subquery (More Intuitive Logic) #

This method uses a correlated subquery in the WHERE clause to check, for each row, if it's the latest entry in its category. It's easier to read at a glance, but can be slower on large tables since the subquery runs once per row.

SELECT category, name
FROM `Table` t1
WHERE created_at = (
    -- Correlated subquery: Get the latest timestamp for t1's category
    SELECT MAX(created_at)
    FROM `Table` t2
    WHERE t2.category = t1.category
);

Breakdown:

• For every row in t1, the subquery looks at all rows in t2 with the same category and finds the maximum created_at.
• If the current row's created_at matches that maximum value, it's included in the result set.

A Quick Note on Edge Cases #

If multiple rows in the same category have the exact same "latest" timestamp (or ID), both methods will return all those rows. If you only want one row per category in this scenario, you can add a LIMIT 1 inside the correlated subquery (or use window functions like ROW_NUMBER(), though that's beyond subqueries).

Let me know if you need to adjust this for your specific table structure or if any part still feels confusing—I’m happy to clarify!

内容的提问来源于stack exchange，提问作者Hans Stefanus