mysql 子查询覆盖字段

SELECT 
    c.customer_id, 
    c.customer_name, 
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count, 
    (SELECT SUM(amount) FROM orders o WHERE o.customer_id = c.customer_id) AS total_amount
FROM 
    customers c;

SELECT 
    c.customer_id, 
    c.customer_name, 
    COUNT(o.order_id) AS order_count, 
    SUM(o.amount) AS total_amount
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name;

CREATE TEMPORARY TABLE temp_orders AS
SELECT 
    customer_id, 
    COUNT(*) AS order_count, 
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    customer_id;

SELECT 
    c.customer_id, 
    c.customer_name, 
    t.order_count, 
    t.total_amount
FROM 
    customers c
JOIN 
    temp_orders t ON c.customer_id = t.customer_id;