mysql - 计算得分高于平均分的男性和女性

Question

这个问题是在练习测试中提出的，我是 SQL 的新手。

问题是确定男女学生的平均分。此外，您还需要确定分数高于其性别平均分的学生人数。

表格说明:

=======================
| Field    |  Type    |  
=======================
| RollNo   |  int     |
| Name     |  text    |
| Gender   |  text    |
| Score    |  text    |
=======================

示例输入:

=========================================
| RollNo   |  Name   | Gender |  Score  |
=========================================
| 101      |  John   |   M    |  85     |
| 102      |  Tracy  |   F    |  79     |
| 103      |  Jake   |   M    |  92     |
| 104      |  Edgar  |   M    |         |
| 105      |  Monica |   F    |  25     |
| 106      |  Alicia |   F    |  50     |
| 107      |  Yash   |   M    |  68     |
=========================================

输出:

=================================
| Gender   |  Count |  AvgScore |
=================================
| Female   |  1     |   51      |
| Male     |  2     |   81      |
=================================

输出说明:
有 4 名男学生，但有 1 名学生没有尝试，所以我们忽略该空值。
所以男性平均分是(85 + 92 + 68)/3 = 81.67(只取整数值)
只有2名学生的分数在81以上

这个我只是为了计算男性和女性的数量而尝试的:

SELECT
CASE WHEN S.Gender="M" then "Male" else "Female" END AS Gender
COUNT(Gender) as Count
FROM Students AS S
GROUP BY Gender

我不知道把 AVG 函数放在哪里。我应该使用嵌套查询吗？或者有什么我可以预先查询 avg 然后与每一行进行比较的东西吗？

Answer 1

您需要进行两次计算。首先是确定您需要的平均值。第二个是检索具有可用平均值的行。您需要的技巧是在第一次计算中使用子查询，并在第二次计算中使用 JOIN。

这是作为子查询制定的第一个计算。

               SELECT Gender, AVG(Score) AvgScore
                 FROM scores
                GROUP BY Gender

这需要像这样加入到您的详细信息中。

   SELECT avg.Gender, avg.AvgScore,
          SUM(scores.Score > avg.AvgScore) AboveAverageCount
     FROM (
               SELECT Gender, AVG(Score) AvgScore
                 FROM scores
                GROUP BY Gender
          ) avg
     JOIN scores ON avg.Gender = scores.Gender
    GROUP BY Gender

看到这个。 https://www.db-fiddle.com/f/jTWnYkAzRfMVkdkM3ve68R/0

AVG() 函数自动省略 NULL 值。

我建议使用 SUM()，因为 score > AvgScore 等比较表达式的值要么为 1 表示真，要么为 0 表示假。这是 MySQL 的一个怪癖，在其他 SQL 方言中不起作用。