問題描述

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.

這篇關于SQL 列在 ORDER BY 中無效，未包含在聚合或 GROUP BY 中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！