mysql复合查询 select

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

-- 错误的连接条件
SELECT 
    c.customer_id, 
    c.customer_name, 
    COUNT(o.order_id) AS total_orders, 
    SUM(o.amount) AS total_amount
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.order_id -- 错误的连接条件
GROUP BY 
    c.customer_id, c.customer_name;

-- 正确的连接条件
SELECT 
    c.customer_id, 
    c.customer_name, 
    COUNT(o.order_id) AS total_orders, 
    SUM(o.amount) AS total_amount
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id -- 正确的连接条件
GROUP BY 
    c.customer_id, c.customer_name;

-- 添加索引
CREATE INDEX idx_customer_id ON customers(customer_id);
CREATE INDEX idx_order_customer_id ON orders(customer_id);

-- 优化后的查询
SELECT 
    c.customer_id, 
    c.customer_name, 
    COUNT(o.order_id) AS total_orders, 
    SUM(o.amount) AS total_amount
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name;