斗鱼笔试 - SQL 划分区间统计成绩百分比

有A,B两表，A（name,subject,score）,B（subject,weight），总分=各科成绩*权重的和，求0~59分，60~89，90~100的人数百分比

CREATE TABLE `a` (
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `b` (
  `subject` varchar(255) DEFAULT NULL,
  `weight` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

没有实现按区间划分统计版本：

SELECT
    COUNT(*) / (
        SELECT
            COUNT(DISTINCT(A.`name`))
        FROM
            A
    )
FROM
    (
        SELECT
            a.`name` AS `name`,
            sum(a.score * b.weight) AS score
        FROM
            A,
            B
        WHERE
            A.`subject` = b.`subject`
        GROUP BY
            a.`name`
    ) student
WHERE
    score >= 90

划分区间：

SELECT
    -- 返回字符串
    ELT(
        -- 划分区间
        INTERVAL (student.score, 0, 60, 90, 100),
        "1-60",
        "61-90",
        "91-100",
        "101-"
    ) AS level,
    count(*) / (
        SELECT
            count(DISTINCT(A.`name`))
        FROM
            A
    ) AS per
FROM
    (
        SELECT
            a.`name` AS `name`,
            sum(a.score * b.weight) AS score
        FROM
            A,
            B
        WHERE
            A.`subject` = b.`subject`
        GROUP BY
            a.`name`
    ) student
GROUP BY
    ELT(
        INTERVAL (student.score, 0, 60, 90, 100),
        "1-60",
        "61-90",
        "91-100",
        "101-"
    );

版本2

SELECT
    -- 返回字符串
    (
        CASE
        WHEN student.score >= 0
        AND student.score < 60 THEN
            "0~59"
        WHEN student.score >= 60
        AND student.score < 90 THEN
            "60~89"
        WHEN student.score >= 90
        AND student.score < 100 THEN
            "90~99"
        ELSE
            "100+"
        END
    ) AS LEVEL,
    (
        count(*) / (
            SELECT
                count(DISTINCT(A.`name`))
            FROM
                A
        )
    ) AS per
FROM
    (
        SELECT
            a.`name` AS `name`,
            sum(a.score * b.weight) AS score
        FROM
            A,
            B
        WHERE
            A.`subject` = b.`subject`
        GROUP BY
            a.`name`
    ) student
GROUP BY
    (
        CASE
        WHEN student.score >= 0
        AND student.score < 60 THEN
            "0~59"
        WHEN student.score >= 60
        AND student.score < 90 THEN
            "60~89"
        WHEN student.score >= 90
        AND student.score < 100 THEN
            "90~99"
        ELSE
            "100+"
        END
    );