SQL Server Average查询

Question

有没有人能告诉我如何查询这个平均值？

我有这张桌子。

 teacher | class | students | rating
 -----------------------------------
   T1        1      001        6
   T1        1      002        6
   T1        2      003        1
   T1        3      004        1
   T2        1      001        6
   T2        1      002        6
   T2        2      003        1
   T2        3      004        1

如果我使用Select teacher, class, avg(rating) from table group by teacher, class，我会得到这样的结果：

teacher | class | rating
------------------------
   T1       1     6
   T1       2     1
   T1       3     1
   T2       1     6
   T2       2     1
   T2       3     1

我想要做的是获得结果的平均rating，并按teacher对其进行分组，即：

teacher | rating
------------------------
   T1      2.66667
   T2      2.66667

因为如果我使用Select avg(rating) from table group by teacher，我会得到

teacher | rating
------------------------
   T1      3.5
   T2      3.5

Answer 1

您可以这样做：

SELECT teacher,avg(avgrating) as avgrating
FROM
(Select teacher, class, avg(rating) avgrating
from TableName 
group by teacher, class) Temp
GROUP BY teacher

结果：

TEACHER   AVGRATING
T1        2.666666
T2        2.666666

格式的示例结果。

OR:

SELECT T1.teacher,avg(T2.avgrating) as rating
FROM TableName T1 JOIN
(SELECT teacher,class,avg(rating) as avgrating
 FROM TableName T2
 GROUP BY teacher,class) T2 ON T1.teacher=T2.teacher
GROUP BY T1.teacher

。

Answer 2

使用嵌套select：

select teacher , avg(avgr) from
 (Select teacher, class, avg(rating) avgr from table group by teacher, class) qry
group by teacher