我試圖通過顯示兩列 CompanyName 和訂單數來了解每個供應商有多少訂單。然后按降序對它們進行排序。
我的 SQL：
SELECT DISTINCT(sp.CompanyName), COUNT(DISTINCT o.OrderID)
FROM Suppliers AS sp
INNER JOIN Products AS p ON sp.SupplierID = p.SupplierID
INNER JOIN Orders AS o ON od.OrderID = o.OrderID
INNER JOIN [Order Details] AS od ON p.ProductID = od.ProductID
AND o.OrderID = od.OrderID
ORDER BY o.OrderDate DESC
但我收到以下錯誤：
如果指定了 SELECT DISTINCT，則訊息 145、級別 15、狀態 1、第 65 行
ORDER BY 項必須出現在選擇串列中。訊息 8120，級別 16，狀態 1，第 65
行列“Suppliers.CompanyName”在選擇串列中無效，因為它不包含在聚合函式或 GROUP BY 子句中。
我的桌子：
uj5u.com熱心網友回復：
正如帖子中的評論中提到的那樣，這樣的事情應該是正確的。
SELECT sp.CompanyName, COUNT(DISTINCT o.OrderID) as orders
FROM Suppliers as sp
INNER JOIN Products as p
ON sp.SupplierID = p.SupplierID
INNER JOIN [Order Details] as od
INNER JOIN Orders as o
ON od.OrderID = o.OrderID
ON p.ProductID = od.ProductID
AND
o.OrderID=od.OrderID
GROUP BY sp.CompanyName;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/510802.html
標籤：sqlsql服务器加入