SQL列按顺序无效，不包含在聚合或组BY中

SELECT TOP 1 e.EmployeeID
FROM Employees e
    INNER JOIN Orders o ON o.EMployeeID = e.EmployeeID
    INNER JOIN (
      SELECT OrderID,
             SUM((UnitPrice * Quantity) - Discount) AS TotalOrderPrice
      FROM [Order Details]
      GROUP BY OrderID
    ) oi ON oi.OrderID = o.OrderID
GROUP BY e.employeeid
ORDER BY TotalOrderPrice * 0.1 DESC,
         COUNT(o.OrderID) ASC

Msg 8127, Level 16, State 1, Line 11

Msg 8127, 16级，状态1，第11行

Column "oi.TotalOrderPrice" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

列“oi。TotalOrderPrice”在ORDER BY子句中无效，因为它不包含在聚合函数或GROUP BY子句中。

1 个解决方案

#1

If you use a GROUP BY, you can only SELECT (and thus, ORDER) the columns, which are

如果您使用一个组，您只能选择(并因此，顺序)列，也就是。

Either one of the columns you grouped by with
用其中一列进行分组
Either is an aggregate function (for example, MAX() or COUNT())
要么是聚合函数(例如MAX()或COUNT()))

MySQL hasn't this limitation, but it is only a MySQL-specific extension to the SQL standard. Any other SQL server, included the Microsoft SQL, have this.

MySQL没有这个限制，但它只是SQL标准的一个特定于SQL的扩展。包括Microsoft SQL在内的任何其他SQL服务器都有这个特性。

#1