MySQL查询最近7笔交易失败卡片时出现未知列cards.id报错

Got it, let's break down why your original query is throwing that error first: MySQL doesn't allow derived tables (like your tmp1 subquery) to reference columns from outer queries that are multiple levels deep. The cards.id you're trying to use in tmp1 lives in the outermost query, which isn't accessible inside that nested derived table.

Instead of nesting subqueries this way, using window functions is a cleaner and more efficient approach here. Here's how to rewrite your query:

WITH ranked_transactions AS (
    SELECT 
        c.id AS card_id,
        t.status,
        -- Assign a rank to each transaction per card, newest first
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY t.id DESC) AS transaction_rank
    FROM cards c
    -- Join to get order cards linked to each card
    JOIN order_cards oc ON c.id = oc.card_id
    -- Join to get transactions for each order card
    JOIN transactions t ON oc.id = t.order_card_id
)
SELECT 
    c.*,
    -- Count how many of the top 7 transactions failed
    COUNT(CASE WHEN rt.status = 'fail' THEN 1 END) AS total_fail
FROM cards c
-- Join back to our ranked transactions to get only the last 7 per card
JOIN ranked_transactions rt ON c.id = rt.card_id
WHERE rt.transaction_rank <= 7
GROUP BY c.id
-- Filter cards where more than 5 of the last 7 failed
HAVING total_fail > 5;

How This Works: #

• CTE (ranked_transactions): This part joins all three tables and uses ROW_NUMBER() to assign a rank to each transaction for every card. The PARTITION BY c.id ensures we rank transactions per card, and ORDER BY t.id DESC makes the most recent transaction rank 1.
• Main Query: We join the original cards table back to the ranked transactions, filter to keep only the first 7 ranks (the newest 7 transactions), then count how many of those are failures. Finally, we filter for cards where that count exceeds 5.

If You're Using an Older MySQL Version (Pre-8.0 Without Window Functions) #

If window functions aren't available, you can use a correlated subquery with variables to rank transactions. Here's an alternative:

SELECT 
    c.*,
    (SELECT COUNT(*)
     FROM (
         SELECT t.status,
                @rank := IF(@current_card = oc.card_id, @rank + 1, 1) AS transaction_rank,
                @current_card := oc.card_id
         FROM order_cards oc
         JOIN transactions t ON oc.id = t.order_card_id
         CROSS JOIN (SELECT @current_card := NULL, @rank := 0) vars
         WHERE oc.card_id = c.id
         ORDER BY t.id DESC
     ) tmp
     WHERE transaction_rank <=7 AND status = 'fail') AS total_fail
FROM cards c
HAVING total_fail >5;

This uses user-defined variables to track the current card and assign ranks, then counts the failed transactions in the top 7. Note that this approach can be less performant on large datasets compared to the window function method.

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