按TypeID分类获取各分类下最新10条记录（不足10条取全部）

Alright, let's tackle this problem: you need to fetch the latest 10 records for each TypeID category, and if a category has fewer than 10 entries, just return all of them. Based on your sample table structure, here are practical solutions for common SQL dialects:

Sample Table Structure #

First, let's recap your table for clarity:

Solution 1: Window Functions (Modern SQL: MySQL 8.0+, PostgreSQL, SQL Server, etc.) #

This is the cleanest approach using ROW_NUMBER() window function, which assigns a unique rank to each row within its TypeID group, ordered by the Created date (newest first).

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY TypeID 
            ORDER BY Created DESC, LID DESC -- Add LID to handle ties in Created date
        ) AS record_rank
    FROM your_table_name -- Replace with your actual table name
) AS ranked_records
WHERE record_rank <= 10;

How it works: #

• PARTITION BY TypeID: Groups the data into subsets based on each TypeID.
• ORDER BY Created DESC: Sorts each group so the newest records come first. Adding LID DESC ensures consistent ordering if multiple records share the same Created date.
• ROW_NUMBER() assigns a number (1, 2, 3...) to each row in the group. We filter to keep only rows where this number is ≤10.

Solution 2: Legacy MySQL (Pre-8.0, No Window Functions) #

If you're working with an older MySQL version that doesn't support window functions, you can use user-defined variables to achieve the same result:

SELECT t.*
FROM (
    SELECT 
        *,
        @current_type := TypeID,
        @rank := IF(@previous_type = TypeID, @rank + 1, 1) AS record_rank,
        @previous_type := @current_type
    FROM your_table_name,
         (SELECT @previous_type := NULL, @rank := 0) AS init_vars
    ORDER BY TypeID, Created DESC, LID DESC
) AS t
WHERE t.record_rank <= 10;

How it works: #

• We initialize two variables: @previous_type to track the current TypeID group, and @rank to count rows within the group.
• The query sorts the data first by TypeID, then by newest Created date.
• For each row, if it's in the same TypeID group as the previous row, we increment the rank; otherwise, we reset the rank to 1.
• Finally, we filter to keep only the first 10 rows per group.

Just remember to replace your_table_name with the actual name of your table, and adjust the sort columns if you need to use a different field (like PaymentDate) to determine "latest" records instead of Created.

内容的提问来源于stack exchange，提问作者John Doe