mysql多表求平均值

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

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob');

INSERT INTO orders (order_id, customer_id, order_amount) VALUES
(1, 1, 150.00),
(2, 1, 200.00),
(3, 2, 75.00);

SELECT 
    c.customer_name,
    AVG(o.order_amount) AS average_order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name;

-- 错误的连接条件
SELECT 
    c.customer_name,
    AVG(o.order_amount) AS average_order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id + 1; -- 错误的连接条件

-- 正确的连接条件
SELECT 
    c.customer_name,
    AVG(o.order_amount) AS average_order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id; -- 正确的连接条件

-- 错误的分组字段
SELECT 
    c.customer_name,
    AVG(o.order_amount) AS average_order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_name; -- 错误的分组字段，可能会有重复的客户名

-- 正确的分组字段
SELECT 
    c.customer_name,
    AVG(o.order_amount) AS average_order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name; -- 正确的分组字段