mysql之case when then 经典用法

表 stu (学生表)

CREATE TABLE stu (
    id INT (10) NOT NULL UNIQUE auto_increment PRIMARY KEY,
    NAME VARCHAR (255) NOT NULL UNIQUE
) ENGINE = INNODB charset = utf8mb4;

INSERT INTO stu (NAME)
VALUES
    ("zhangsan"),
    ("list"),
    ("wanger"),
    ("mazi"),
    ("wu"),
    ("liu"),
    ("qi"),
    ("ba"),
    ("jiu"),
    ("ten"),
    ("eleven"),
    ("twelve"),
    ("thirteen"),
    ("fourteen");

ALTER TABLE stu ADD COLUMN sex VARCHAR (2) NOT NULL DEFAULT 'm';

UPDATE stu
SET sex = 'wm'
WHERE
    id IN (2, 4, 6, 8, 10, 12);

表score(成绩表)

CREATE TABLE score (
    id INT (10) NOT NULL UNIQUE auto_increment PRIMARY KEY,
    sid INT (10) NOT NULL,
    type INT (10) NOT NULL,
    score INT (10) NOT NULL
) ENGINE = INNODB charset = utf8mb4;

INSERT INTO score (sid, type, score)
VALUES
    (1, 1, 60),
    (1, 2, 70),
    (2, 1, 50),
    (2, 2, 80),
    (3, 1, 31),
    (3, 2, 68),
    (4, 1, 10),
    (4, 2, 90),
    (5, 1, 35),
    (5, 2, 60),
    (6, 1, 56),
    (6, 2, 85),
    (7, 1, 52),
    (7, 2, 78),
    (8, 1, 87),
    (8, 2, 56),
    (9, 1, 45),
    (9, 2, 43),
    (10, 1, 54),
    (10, 2, 33),
    (11, 1, 67),
    (11, 2, 53),
    (12, 1, 85),
    (12, 2, 78),
    (13, 1, 84),
    (13, 2, 93),
    (14, 1, 74),
    (14, 2, 48);

表course

CREATE TABLE course (
    id INT (10) NOT NULL UNIQUE auto_increment PRIMARY KEY,
    NAME VARCHAR (255) NOT NULL
) ENGINE = INNODB charset = utf8mb4;

INSERT INTO course (NAME)
VALUES
    ("china"),
    ("english");

需求：英语（english）排名前十的人中男女比例

首先查询排名前十的人的性别

SELECT
    stu.sex
FROM
    stu
LEFT JOIN score s ON stu.id = s.sid
LEFT JOIN course c ON s.type = c.id
WHERE
    c. NAME = 'english'
ORDER BY
    s.score DESC
LIMIT 0,
 10

结果：

case when then 分组累加

SELECT
    count(*) AS tc,
    sum(
        CASE sex
        WHEN 'm' THEN
            1
        ELSE
            0
        END
    ) / count(*) AS 'mp',
    sum(
        CASE sex
        WHEN 'wm' THEN
            1
        ELSE
            0
        END
    ) / count(*) AS 'wp'
FROM
    (
        SELECT
            stu.sex
        FROM
            stu
        LEFT JOIN score s ON stu.id = s.sid
        LEFT JOIN course c ON s.type = c.id
        WHERE
            c. NAME = 'english'
        ORDER BY
            s.score DESC
        LIMIT 0,
        10
    ) t;

结果：

需求2：最偏科的十名学生，（中文-英语）分差最大

首先拿到需要的信息，course.name, score.score, score.sid

SELECT
    sc.score,
    c.NAME,
    sc.sid
FROM
    score sc
LEFT JOIN course c ON sc.type = c.id

case when then + group by 分组

SELECT
    s.sid,
    max(
        CASE s. NAME
        WHEN 'english' THEN
            score
        ELSE
            0
        END
    ) 'e',
    max(
        CASE s. NAME
        WHEN 'china' THEN
            score
        ELSE
            0
        END
    ) 'c'
FROM
    (
        SELECT
            sc.score,
            c. NAME,
            sc.sid
        FROM
            score sc
        LEFT JOIN course c ON sc.type = c.id
    ) s
GROUP BY
    s.sid

order by + limit + left join

SELECT
    stu.id,
    abs(e - c) AS dif,
    stu. NAME,
    t.e,
    t.c
FROM
    (
        SELECT
            s.sid,
            max(
                CASE s. NAME
                WHEN 'english' THEN
                    score
                ELSE
                    0
                END
            ) 'e',
            max(
                CASE s. NAME
                WHEN 'china' THEN
                    score
                ELSE
                    0
                END
            ) 'c'
        FROM
            (
                SELECT
                    sc.score,
                    c. NAME,
                    sc.sid
                FROM
                    score sc
                LEFT JOIN course c ON sc.type = c.id
            ) s
        GROUP BY
            s.sid
    ) t
LEFT JOIN stu ON t.sid = stu.id
ORDER BY
    dif DESC
LIMIT 0,
 10;

另一种写法：

SELECT
    s.sid,
    abs(
        max(
            CASE s. NAME
            WHEN 'english' THEN
                score
            ELSE
                0
            END
        ) - max(
            CASE s. NAME
            WHEN 'china' THEN
                score
            ELSE
                0
            END
        )
    ) AS dif
FROM
    (
        SELECT
            sc.score,
            c. NAME,
            sc.sid
        FROM
            score sc
        LEFT JOIN course c ON sc.type = c.id
    ) s
LEFT JOIN stu ON s.sid = stu.id
GROUP BY
    stu.id
ORDER BY
    dif DESC
LIMIT 0,
 10;