时间戳

首页 > 解决方案 > Order by 子句无效

sql - Order by 子句无效

问题描述

我想找出前 5 名制造商中每种型号的平均价格，就销售数量而言，并按平均价格订购。

我试过下面的代码但得到错误 -

消息 1033，级别 15，状态 1，第 602 行
ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP、OFFSET 或 FOR XML。

任何人都可以帮我纠正它吗？

SELECT TOP 5
    Manufacturer_Name AS [Manufacturer],
    AVG(TotalPrice) AS [average total price],
    SUM(Quantity) AS [sales quantity]
FROM 
    FACT_TRANSACTIONS T1 
LEFT JOIN 
    DIM_MODEL M1 ON T1.IDModel = M1.IDModel 
INNER JOIN 
    DIM_MANUFACTURER M2 ON M1.IDManufacturer = M2.IDManufacturer
WHERE
    T1.IDModel IN (SELECT T1.IDModel FROM FACT_TRANSACTIONS T1
                   GROUP BY T1.IDModel
                   ORDER BY AVG(T1.TotalPrice) DESC)
GROUP BY 
    Manufacturer_Name

标签： sqlsql-server

解决方案

你有ORDER BY AVG(T1.TotalPrice) DESC一个子查询，从而导致错误。ORDER BY对子查询没有影响，因为它只会真正改变最后显示的结果的顺序，从而改变错误（除非你将它与 TOP ... 结合使用，然后它会用于不同的目的并被允许）。

应该是这样的：

SELECT TOP 5
Manufacturer_Name as [Manufacturer],
AVG(TotalPrice) as [average total price],
sum(Quantity) as [sales quantity]
FROM FACT_TRANSACTIONS T1 LEFT JOIN DIM_MODEL M1
ON T1.IDModel=M1.IDModel INNER JOIN DIM_MANUFACTURER M2 ON M1.IDManufacturer=M2.IDManufacturer
where T1.IDModel in (select T1.IDModel from FACT_TRANSACTIONS T1
group by T1.IDModel)
GROUP BY Manufacturer_Name

推荐阅读