SQL 列在 ORDER BY 中无效，未包含在聚合或 GROUP BY 中

SELECT TOP 1 e.EmployeeID来自员工 eINNER JOIN 订单 o ON o.EmployeeID = e.EmployeeID内部联接 (选择订单 ID，SUM((UnitPrice * Quantity) - Discount) AS TotalOrderPriceFROM [订单详情]按订单 ID 分组) oi ON oi.OrderID = o.OrderIDGROUP BY e.employeeidORDER BY TotalOrderPrice * 0.1 DESC,COUNT(o.OrderID) ASC

<块引用>

消息 8127，级别 16，状态 1，第 11 行

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

解决方案

如果你使用 GROUP BY，你只能SELECT(因此，ORDER) 列，它们是

1. 您分组所依据的任一列
2. 要么是聚合函数(例如，MAX() 或 COUNT())

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

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

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.

解决方案

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

1. Either one of the columns you grouped by with
2. Either is an aggregate function (for example, MAX() or 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.