Sql -使用left outer join更正基表的字段总和

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

-- 创建 customers 表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- 插入一些示例数据
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders (order_id, customer_id, amount) VALUES (1, 1, 100.00), (2, 1, 150.00), (3, 2, 75.00);

-- 使用 LEFT OUTER JOIN 计算每个客户的订单总和
SELECT 
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM 
    customers c
LEFT OUTER JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name;

COALESCE(SUM(o.amount), 0) AS total_amount

-- 创建索引
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 使用子查询优化
SELECT 
    c.customer_id,
    c.customer_name,
    COALESCE(total_amount, 0) AS total_amount
FROM 
    customers c
LEFT OUTER JOIN (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id;