sql-server - 按子句排序的平均值

Question

我有这个查询:

SELECT 
   Field1
   AVG(Field2) as Field2, 
   AVG(Field3) as Field3, 
   AVG(Field4) as Field4 
FROM Table1
GROUP BY Field1
ORDER BY Field2 DESC, Field3 DESC, Field4 DESC

如何(如果可能)用三个字段的平均值进行排序？我想在得到三个字段的平均值后下单。例如:

...
ORDER BY (Field2 + Field3 + Field4) / 3

Answer 1

要根据 Field1 + Field2 + Field3 的平均值进行排序，您可以添加一个新字段并对其进行排序。

select Field1,   
       avg(Field2) as AField2, 
       avg(Field3) as AField3, 
       avg(Field4) as AField4,
       avg(Field2+Field3+Field4) as Sort
from Table1
group by Field1
order by Sort desc

或者您可以将查询放入子查询中，并根据外部查询中的字段总和进行排序。

select T.Field1,
       T.AField2,
       T.AField3,
       T.AField4
from (select Field1,   
             avg(Field2) as AField2, 
             avg(Field3) as AField3, 
             avg(Field4) as AField4
      from Table1
      group by Field1) as T
order by T.AField2 + T.AField3 + T.AField4 desc