在SQL中使用Count和group by的内部连接

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

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'),
(3, 'Charlie');

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2023-01-01'),
(102, 1, '2023-02-01'),
(103, 2, '2023-03-01'),
(104, 3, '2023-04-01'),
(105, 3, '2023-05-01');

-- 使用内部连接、COUNT 和 GROUP BY 统计每个客户的订单数量
SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_name;

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_name;