mysql 多表统计个数

SELECT c.customer_id, 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, c.customer_name;

-- 创建示例表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

-- 插入示例数据
INSERT INTO students (student_id, student_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO courses (course_id, student_id, course_name) VALUES
(101, 1, 'Math'),
(102, 1, 'Science'),
(103, 2, 'History');

-- 查询每个学生选修的课程数量
SELECT s.student_id, s.student_name, COUNT(c.course_id) AS course_count
FROM students s
LEFT JOIN courses c ON s.student_id = c.student_id
GROUP BY s.student_id, s.student_name;