斗鱼笔试
有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
);
相关知识
斗鱼虎牙财报亮眼，游戏直播背后的狂欢与隐忧
玩家公认斗鱼吃鸡一哥！枪稳人骚花老湿，受邀直通精英擂台赛
园林景观设计笔试试题
2020年辽宁事业单位笔试模拟试题
嵌入式笔试试题6篇(全文)
广西壮族自治区地质环境监测站2024年度公开招聘工作人员笔试公告
省考面试1分=笔试2分！面试真的太重要了！
2024安徽皖新融资租赁有限公司服务人员招聘公告笔试工作通知
关于安徽皖新融资租赁有限公司服务人员招聘公告笔试工作通知
普洱市生态环境局2019年遴选公务员笔试通知
网址: 斗鱼笔试 https://m.huajiangbk.com/newsview567209.html
| 上一篇: XML基本解析 |
下一篇: [鲜花]汉字书写笔顺... |
