MySQL查询：获取每个客户最新交易日期的最晚交易时间

Hey there! I get exactly where you're stuck—when you try combining max(Datetrans) and max(Timetrans) directly, the database just grabs the absolute largest values across the whole table, not the latest time on the latest date for each customer. Let's fix this with two reliable approaches:

Approach 1: Use Window Functions (Clean & Intuitive) #

Window functions like ROW_NUMBER() let you rank transactions per customer, so you can pick the exact record you need:

SELECT CustomerID, Datetrans, Timetrans
FROM (
    SELECT 
        CustomerID,
        Datetrans,
        Timetrans,
        -- Rank transactions: newest date first, then latest time on that date
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Datetrans DESC, Timetrans DESC) AS transaction_rank
    FROM Transaction
) ranked_transactions
WHERE transaction_rank = 1;

How this works: #

• The inner query adds a transaction_rank column for each customer: it sorts their transactions from newest date to oldest, and for the same date, from latest time to earliest.
• The outer query filters for transaction_rank = 1, which gives you the single most recent transaction (latest date + latest time that day) per customer.

Approach 2: Subquery + Join (Great for Older SQL Versions) #

If window functions aren't available in your SQL dialect, this two-step method works perfectly:

SELECT 
    t.CustomerID,
    t.Datetrans,
    MAX(t.Timetrans) AS Latest_Time_On_Latest_Date
FROM Transaction t
-- First, get the latest date for each customer
INNER JOIN (
    SELECT CustomerID, MAX(Datetrans) AS Latest_Date
    FROM Transaction
    GROUP BY CustomerID
) customer_latest_date 
    ON t.CustomerID = customer_latest_date.CustomerID 
    AND t.Datetrans = customer_latest_date.Latest_Date
-- Now get the latest time on that specific date
GROUP BY t.CustomerID, t.Datetrans;

How this works: #

1. The subquery customer_latest_date finds the most recent transaction date for each customer.
2. We join this back to the original table to only keep transactions from that latest date for each customer.
3. Finally, we use MAX(Timetrans) to grab the latest time from those filtered transactions.

Note: #

If a customer has two transactions at the exact same latest time on their latest date, the second approach will return a single row with that time, while the window function approach will pick one of the rows (you can use RANK() instead of ROW_NUMBER() if you want to keep all ties).

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