按供应商统计订单数时出现SQL错误，求正确实现方案

需要统计每个供应商的订单数量，展示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

执行后出现以下错误：

Msg 145, Level 15, State 1, Line 65
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Msg 8120, Level 16, State 1, Line 65
Column 'Suppliers.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

涉及表结构：Suppliers、Products、Orders、Order Details，关联关系为：

• Suppliers.SupplierID 关联 Products.SupplierID
• Products.ProductID 关联 [Order Details].ProductID
• Orders.OrderID 关联 [Order Details].OrderID

核心错误点 #

1. 逻辑混淆：用SELECT DISTINCT替代GROUP BY做分组统计，这是错误用法——统计分组聚合结果必须用GROUP BY，DISTINCT仅用于去重查询结果行。
2. 排序字段错误：需求是按订单数降序，但原语句用了o.OrderDate，且SELECT DISTINCT要求排序字段必须在查询列表中，同时该字段和统计需求无关。
3. 关联顺序错误：先关联Orders时[Order Details]（别名od）还未被引入，导致引用未定义的表别名错误。

修正后的SQL #

SELECT 
    sp.CompanyName, 
    COUNT(DISTINCT o.OrderID) AS 订单数
FROM Suppliers AS sp
INNER JOIN Products AS p 
    ON sp.SupplierID = p.SupplierID
INNER JOIN [Order Details] AS od 
    ON p.ProductID = od.ProductID
INNER JOIN Orders AS o 
    ON od.OrderID = o.OrderID
GROUP BY sp.CompanyName
ORDER BY 订单数 DESC

关键说明 #

• 调整关联顺序：先关联供应商到产品，再到订单详情，最后关联订单，避免未定义表的引用错误。
• 使用GROUP BY sp.CompanyName：按供应商名称分组，确保COUNT函数统计的是每个供应商对应的订单数量。
• COUNT(DISTINCT o.OrderID)：避免同一订单包含多个该供应商产品时重复计数，保证订单数统计准确。
• ORDER BY 订单数 DESC：直接按统计出的订单数量降序排序，符合需求。

内容的提问来源于stack exchange，提问作者Mia Larsen