有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
);