SQL如何查询金额最高的10张发票及对应发票总金额

代码修正说明 #

原有SQL存在两处语法错误：

• TOP(10) 后未指定需要返回的字段
• 嵌套的子查询未设置别名，数据库无法识别该结果集

适配不同数据库的正确写法

SQL Server 版本

SELECT TOP(10) InvoiceId, Total
FROM (
    SELECT s.InvoiceId, SUM(l.Quantity * p.UnitPrice) AS Total
    FROM tbl_Line AS l
    JOIN tbl_Sales AS s ON l.InvoiceId = s.InvoiceId
    JOIN tbl_Products AS p ON p.ProductId = l.ProductId
    GROUP BY s.InvoiceId
) AS InvoiceTotal
ORDER BY Total DESC

MySQL / PostgreSQL 版本

SELECT InvoiceId, Total
FROM (
    SELECT s.InvoiceId, SUM(l.Quantity * p.UnitPrice) AS Total
    FROM tbl_Line AS l
    JOIN tbl_Sales AS s ON l.InvoiceId = s.InvoiceId
    JOIN tbl_Products AS p ON p.ProductId = l.ProductId
    GROUP BY s.InvoiceId
) AS InvoiceTotal
ORDER BY Total DESC
LIMIT 10

优化简化写法

如果不需要关联sales表的额外字段，无需嵌套子查询，可直接分组后排序取数，执行效率更高：

-- SQL Server 简化版
SELECT TOP(10) s.InvoiceId, SUM(l.Quantity * p.UnitPrice) AS Total
FROM tbl_Line AS l
JOIN tbl_Sales AS s ON l.InvoiceId = s.InvoiceId
JOIN tbl_Products AS p ON p.ProductId = l.ProductId
GROUP BY s.InvoiceId
ORDER BY Total DESC

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