GROUP BY + CASE 语句

使用 GROUP BY + CASE 语句实现数据分组统计

sql
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    gender VARCHAR(10),
    age INT,
    class VARCHAR(10)
);
INSERT INTO students (id, name, gender, age, class)
VALUES
    (1, '张三', '男', 18, '一班'),
    (2, '李四', '女', 17, '一班'),
    (3, '王五', '男', 19, '二班'),
    (4, '赵六', '女', 18, '二班'),
    (5, '陈七', '男', 17, '三班'),
    (6, '刘八', '女', 18, '三班'),
    (7, '杨九', '男', 19, '四班'),
    (8, '周十', '女', 17, '四班');

sql
SELECT 
    class,
    COUNT(CASE WHEN gender = '男' THEN 1 ELSE NULL END) AS male_count,
    COUNT(CASE WHEN gender = '女' THEN 1 ELSE NULL END) AS female_count
FROM
    students
GROUP BY class;

+-------+------------+--------------+
| class | male_count | female_count |
+-------+------------+--------------+
| 一班  |          1 |            1 |
| 二班  |          1 |            1 |
| 三班  |          1 |            1 |
| 四班  |          1 |            1 |
+-------+------------+--------------+