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_date DATE
);

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

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2023-01-01'),
(102, 1, '2023-01-15'),
(103, 2, '2023-02-01');

-- 使用子查询查询每个客户的订单总数
SELECT 
    c.customer_name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM 
    customers c;

-- 使用JOIN代替子查询
SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id;

-- 使用聚合函数确保子查询返回单个值
SELECT 
    c.customer_name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM 
    customers c;