如何编写简单的自连接SQL查询，找出对应最大ID且nodeName为'Delay'的订单ID

Hey there! I get that you're a Java dev diving into SQL, and self-joins can feel a bit tricky at first—let's break this down clearly for you.

Approach #

The core idea here is to first identify the maximum id for each orderId, then join this result back to the original table to check if the corresponding nodeName is 'Delay'. Here's how it works step by step:

• Create a subquery that groups records by orderId and calculates the maximum id for each group.
• Perform a self-join between the original table and this subquery, matching on both orderId and the maximum id value.
• Filter the joined results to only keep rows where nodeName equals 'Delay'—the orderIds from these rows are exactly what you need.

SQL Query #

SELECT DISTINCT t.orderId
FROM your_table t
JOIN (
    -- Subquery to get max id per orderId
    SELECT orderId, MAX(id) AS max_id
    FROM your_table
    GROUP BY orderId
) max_ids ON t.orderId = max_ids.orderId AND t.id = max_ids.max_id
WHERE t.nodeName = 'Delay';

Explanation #

• The subquery max_ids generates a list of each orderId paired with its highest id value. For your sample data, this would return rows like (2,4), (5,8), (3,11), (1,15).
• The self-join links each row in the original table t to the corresponding max_id entry for its orderId. This ensures we only look at the latest (highest id) record for each orderId.
• The WHERE clause filters these latest records to only those where nodeName is 'Delay', and DISTINCT ensures we don't get duplicate orderIds (though in this case, it's redundant since each orderId has only one max id, but it's a safe practice).

Verification with Your Sample Data #

Running this query on your provided data will return orderId 3 and 1:

• For orderId=3, the max id is 11, and nodeName is 'Delay' → included.
• For orderId=1, the max id is 15, and nodeName is 'Delay' → included.
• For orderId=2 (max id 4, nodeName 'Winter') and orderId=5 (max id 8, nodeName 'Winter') → excluded, which matches your expected result.

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