SQL -学生in列表和2个小数点后的平均分

Question

问题:编写一个查询，以显示学生ids列表和平均分数，如果它们的平均分数大于80，则以两个小数位显示。为average标记为avg_mark指定别名。根据平均分对结果进行排序。

Schema for reference

我写了这个查询，但是结果与预期的输出不匹配。

select student_id, round(avg(value)) as avg_mark 
from mark
where value>80    -------well this is wrong as I need to compare with an average of value
group by student_id
order by avg_mark

运行此查询会给出以下链接的错误输出

Output

谢谢！

Answer 1

你想要having而不是where

select student_id, round(avg(value)) as avg_mark 
from mark
group by student_id
having avg(value) > 80 
order by avg_mark

Answer 2

请改用此函数

round(avg(value), 2)

这会有帮助的

Answer 3

下面的代码应该可以正常工作。

select student_id, cast(avg(value) as decimal(10,2)) as avg_mark from mark
group by student_id
having avg(value)>80
order by avg_mark;