用sql查询,两个班级参加考试,每个班级考三门,满分为100,每个班级人数都为50人,查询每个班级中单科成绩高于平均分的人数,并求和
时间: 2023-09-13 21:09:45 浏览: 56
好的,可以使用如下的 SQL 查询语句来实现:
```
SELECT
class,
COUNT(*) AS num_above_avg,
SUM(score - avg_score) AS sum_above_avg
FROM (
SELECT
class,
subject,
score,
AVG(score) OVER (PARTITION BY class, subject) AS avg_score
FROM scores
WHERE
(class = 'Class A' OR class = 'Class B') AND
subject IN ('Math', 'English', 'Science')
) AS subquery
WHERE score > avg_score
GROUP BY class;
```
其中,假设我们的成绩表为 `scores`,包含以下列:
- `class`: 班级名称,取值为 `'Class A'` 或 `'Class B'`
- `subject`: 科目名称,取值为 `'Math'`、`'English'` 或 `'Science'`
- `score`: 分数,取值为 0 到 100
该查询语句的大致思路如下:
1. 在 `scores` 表中筛选出班级为 `'Class A'` 或 `'Class B'`,科目为 `'Math'`、`'English'` 或 `'Science'` 的所有成绩记录,并计算出每个班级、每门科目的平均分,作为子查询的结果。
2. 在子查询的结果中,筛选出分数高于平均分的所有成绩记录,并记录下班级、科目、分数和平均分。
3. 在上一步的结果基础上,按班级分组统计每个班级中单科成绩高于平均分的人数(即记录数量),并计算出这些人的分数总和。
最终的查询结果应该包含两列:
- `class`: 班级名称,取值为 `'Class A'` 或 `'Class B'`
- `num_above_avg`: 单科成绩高于平均分的人数之和
- `sum_above_avg`: 单科成绩高于平均分的所有人的分数之和
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)