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

来源：互联网发布：知乎的分割线编辑：程序博客网时间：2024/04/19 16:59

有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`    ) studentWHERE    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 perFROM    (        SELECT            a.`name` AS `name`,            sum(a.score * b.weight) AS score        FROM            A,            B        WHERE            A.`subject` = b.`subject`        GROUP BY            a.`name`    ) studentGROUP 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 perFROM    (        SELECT            a.`name` AS `name`,            sum(a.score * b.weight) AS score        FROM            A,            B        WHERE            A.`subject` = b.`subject`        GROUP BY            a.`name`    ) studentGROUP 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    );

0 0