mysql - 哪里和群冲突了？

Question

我有一个这样的表:

mysql> select * from studentscore;
+------------+-----------+-------+
| student_id | cource_id | score |
+------------+-----------+-------+
|          1 |         1 |    80 |
|          1 |         2 |    90 |
|          1 |         3 |    85 |
|          1 |         4 |    78 |
|          2 |         2 |    53 |
|          2 |         3 |    77 |
|          2 |         5 |    80 |
|          3 |         1 |    71 |
|          3 |         2 |    70 |
|          3 |         4 |    80 |
|          3 |         5 |    65 |
|          3 |         6 |    75 |
|          4 |         2 |    90 |
|          4 |         3 |    80 |
|          4 |         4 |    70 |
|          4 |         6 |    95 |
|          5 |         1 |    60 |
|          5 |         2 |    70 |
|          5 |         5 |    80 |
|          5 |         6 |    69 |
|          6 |         1 |    76 |
|          6 |         2 |    88 |
|          6 |         3 |    87 |
|          7 |         4 |    80 |
|          8 |         2 |    71 |
|          8 |         3 |    58 |
|          8 |         5 |    68 |
|          9 |         2 |    88 |
|         10 |         1 |    77 |
|         10 |         2 |    76 |
|         10 |         3 |    80 |
|         10 |         4 |    85 |
|         10 |         5 |    83 |
|         11 |         3 |    80 |
|         12 |         4 |    99 |
|         13 |         5 |    74 |
+------------+-----------+-------+

我想显示 student_id 和高于 80 的学生的平均分数。

我想要的输出是这样的:

+------------+-------------------+
| student_id | Average           |
+------------+-------------------+
|          1 |             83.25 |
|          4 |             83.75 |
|          6 | 83.66666666666667 | // and how can I make this result shorter like 83.67?
|          7 |                80 |
|          9 |                88 |
|         10 |              80.2 |
|         11 |                80 |
|         12 |                99 |
+------------+-------------------+

我试过以下代码

mysql> select   student_id, avg(score) as average_score
    -> from     studentscore
    -> group by student_id
    -> where    avg(score) >= 80;

它给了我一个语法错误。

我知道规则 where 子句应该放在 group by 子句之前，但我不能，因为 where 子句取决于 group by 子句的结果，如果我改变它们的位置，它会给我另一个错误(“群组功能使用无效").

谁能告诉我如何获得我想要的 table ？

Answer 1

用“有”代替“哪里”