查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
时间: 2023-12-12 20:02:58 浏览: 30
```
SELECT
c.cid AS 课程ID,
c.tname AS 课程name,
MAX(score) AS 最高分,
MIN(score) AS 最低分,
AVG(score) AS 平均分,
CONCAT(CAST(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) AS CHAR(10)), '/', CAST(COUNT(*) AS CHAR(10))) AS 及格率,
CONCAT(CAST(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) AS CHAR(10)), '/', CAST(COUNT(*) AS CHAR(10))) AS 中等率,
CONCAT(CAST(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) AS CHAR(10)), '/', CAST(COUNT(*) AS CHAR(10))) AS 优良率,
CONCAT(CAST(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS CHAR(10)), '/', CAST(COUNT(*) AS CHAR(10))) AS 优秀率
FROM
sc
INNER JOIN course c ON sc.cid = c.cid
GROUP BY
c.cid,
c.tname;
```