sql server语言 - SQL LIMIT 1 DESC order

Question

我试图只显示每个工作中心最畅销的产品，但它一直告诉我LIMIT 1是不正确的语法。我似乎在这里找不到有帮助的答案，所以我决定问这个问题。这是我的疑问。

SELECT WorkCenter.WorkCenterCode, Product.Name, SUM(CustomerOrderLine.Quantity*CustomerOrderLine.ActualPrice) AS 'Total Sales'
FROM WorkCenter 
    INNER JOIN Product ON WorkCenter.WorkCenterCode = Product.WorkCenterCode 
    INNER JOIN CustomerOrderLine ON Product.ProductID = CustomerOrderLine.ProductID
    GROUP BY WorkCenter.WorkCenterCode, Product.Name
    ORDER BY 'Total Sales' DESC 
    LIMIT 1

Answer 1

以下是您的查询清理过的部分：

SELECT  wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales"
FROM WorkCenter wc INNER JOIN
     Product p
     ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
     CustomerOrderLine col
     ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
ORDER BY "Total Sales" DESC
LIMIT 1

请注意从双引号到单引号的重要变化。这对于order by子句尤其重要，因此该子句实际上会执行某些操作，而不是按常量排序。添加表别名使查询更易于阅读。

如果您使用的是Visual Studio，则应该使用top而不是limit:

SELECT TOP 1 wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales"
FROM WorkCenter wc INNER JOIN
     Product p
     ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
     CustomerOrderLine col
     ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
ORDER BY "Total Sales" DESC;

编辑：

对于每个工作中心一行，将其用作row_number():的子查询

SELECT WorkCenterCode, Name, "Total Sales"
FROM (SELECT wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales",
             row_number() over (partition by wc.WorkCenterCode order by SUM(col.Quantity*col.ActualPrice) desc) as seqnum
      FROM WorkCenter wc INNER JOIN
           Product p
           ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
           CustomerOrderLine col
           ON p.ProductID = col.ProductID
      GROUP BY wc.WorkCenterCode, p.Name
     ) t
WHERE seqnum = 1
ORDER BY "Total Sales" DESC;

Answer 2

对于SQL Server，使用SELECT TOP 1

SELECT TOP 1 WorkCenter.WorkCenterCode, Product.Name, SUM(CustomerOrderLine.Quantity*CustomerOrderLine.ActualPrice) AS [Total Sales]
FROM            WorkCenter INNER JOIN
                     Product ON WorkCenter.WorkCenterCode = Product.WorkCenterCode INNER     JOIN
                     CustomerOrderLine ON Product.ProductID = CustomerOrderLine.ProductID
GROUP BY WorkCenter.WorkCenterCode, Product.Name
ORDER BY [Total Sales] DESC 

Answer 3

这是关于使用ROW_NUMBER()的主题的另一个SO问题。CCD_ 7总共只返回一行，而CCD_。