MySQL关联表仅获取最多4行数据的技术问题求助

Hey there! I get that you're stuck with joining your users and user_transections tables while limiting each user to a maximum of 4 transaction records—even after Googling, you haven't found the right solution. Let's fix that together.

The Core Idea #

The best way to handle this is to rank transactions per user first, then filter to keep only the top 4 entries before joining with the users table. This works across most modern databases (like MySQL 8+, PostgreSQL, SQL Server, etc.) using window functions.

Solution 1: Using Window Functions (Recommended for Modern Databases) #

Here's a clean, efficient query that does exactly what you need:

SELECT u.id, u.name, ut.amount, ut.created_at
FROM users u
INNER JOIN (
    -- Subquery to rank transactions per user
    SELECT 
        user_id,
        amount,
        created_at,
        -- Assign a unique number to each transaction for the same user, ordered by creation time (newest first)
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY created_at DESC
        ) AS transaction_rank
    FROM user_transections
) ut ON u.id = ut.user_id
-- Keep only the first 4 transactions per user
WHERE ut.transaction_rank <= 4;

Breakdown:

• PARTITION BY user_id groups transactions by each user.
• ORDER BY created_at DESC sorts transactions so the newest ones come first (swap this with created_at ASC if you want oldest first, or amount DESC if you want largest amounts first).
• The transaction_rank column assigns a number from 1 to N for each user's transactions. We filter to keep only ranks 1-4.

Solution 2: For Older Databases (e.g., MySQL 5.x Without Window Functions) #

If you're using an older database that doesn't support window functions, you can use variables to achieve the same result:

SELECT u.id, u.name, ut.amount, ut.created_at
FROM users u
INNER JOIN (
    SELECT 
        user_id,
        amount,
        created_at,
        -- Use variables to track and increment the transaction count per user
        @transaction_rank := IF(@current_user = user_id, @transaction_rank + 1, 1) AS transaction_rank,
        @current_user := user_id
    FROM user_transections,
         -- Initialize variables
         (SELECT @current_user := NULL, @transaction_rank := 0) vars
    -- Order by user first, then transaction date (adjust as needed)
    ORDER BY user_id, created_at DESC
) ut ON u.id = ut.user_id
WHERE ut.transaction_rank <= 4;

Breakdown:

• We use @current_user to track which user we're currently processing.
• @transaction_rank increments by 1 for each transaction of the same user, and resets to 1 when we switch to a new user.
• Again, we filter to keep only the first 4 transactions per user.

Customization Tip #

If you want to sort transactions by something other than created_at (like transaction amount), just change the ORDER BY clause in the subquery to match your needs.

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