SQL - 聚合函数中的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16317814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Subquery in Aggregate Function
提问by Thomas
I'm using the northwind database to refresh my SQL skills by creating some more or less complex queries. Unfortunately I could not find a solution for my last use case: "Get the sum of the five greatest orders for every category in year 1997."
我正在使用 Northwind 数据库通过创建一些或多或少复杂的查询来更新我的 SQL 技能。不幸的是，我找不到最后一个用例的解决方案：“获取 1997 年每个类别的五个最大订单的总和。”
The tables involved are:
涉及的表是：
Orders(OrderId, OrderDate)
Order Details(OrderId, ProductId, Quantity, UnitPrice)
Products(ProductId, CategoryId)
Categories(CategoryId, CategoryName)
I have tried the following query
我尝试了以下查询
SELECT c.CategoryName, SUM(
(SELECT TOP 5 od2.UnitPrice*od2.Quantity
FROM [Order Details] od2, Products p2
WHERE od2.ProductID = p2.ProductID
AND c.CategoryID = p2.CategoryID
ORDER BY 1 DESC))
FROM [Order Details] od, Products p, Categories c, Orders o
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName
Well... It turned out that subqueries are not allowed in aggregate functions. I've read other posts about this issue but could not find a solution for my specific use case. Hope you can help me out...
嗯...原来在聚合函数中不允许子查询。我已阅读有关此问题的其他帖子，但找不到适合我的特定用例的解决方案。希望你能帮帮我...
回答by Gordon Linoff
Subqueries are not generally allowed in aggregate functions. Instead, move the aggregate insidethe subquery. In this case, you'll need an extra level of subquery because of the top 5:
聚合函数中通常不允许使用子查询。相反，在子查询内移动聚合。在这种情况下，您将需要额外级别的子查询，因为top 5：
SELECT c.CategoryName,
(select sum(val)
from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val
FROM [Order Details] od2, Products p2
WHERE od2.ProductID = p2.ProductID
AND c.CategoryID = p2.CategoryID
ORDER BY 1 DESC
) t
)
FROM [Order Details] od, Products p, Categories c, Orders o
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName, c.CategoryId
回答by Aleksandr Fedorenko
Use CTEwith ROW_NUMBERranking function instead of excessive subquery.
将CTE与ROW_NUMBER排名函数一起使用，而不是过多的子查询。
;WITH cte AS
(
SELECT c.CategoryName, od2.UnitPrice, od2.Quantity,
ROW_NUMBER() OVER(PARTITION BY c.CategoryName ORDER BY od2.UnitPrice * od2.Quantity DESC) AS rn
FROM [Order Details] od JOIN Products p ON od.ProductID = p.ProductID
JOIN Categories c ON p.CategoryID = c.CategoryID
JOIN Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101'), 0)
AND o.OrderDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101')+1, 0)
)
SELECT CategoryName, SUM(UnitPrice * Quantity) AS val
FROM cte
WHERE rn < 6
GROUP BY CategoryName
回答by Ian P
Its definitely a sub query problem here is an excellent articleon this (originally written for Access but the syntax is identical), also orderdate = 1997 will give order date for 1 jan 1997' -- you need datepart(year, orderdate) = 1997, once you have the (up to five) rows returned for each category you can then encapsulate the rows returned and agregate them
它绝对是一个子查询问题，这是一篇关于此的优秀文章（最初是为 Access 编写的，但语法是相同的），orderdate = 1997 也将给出 1 jan 1997 的订单日期'——你需要 datepart(year, orderdate) = 1997 ，一旦您为每个类别返回了（最多五行）行，您就可以封装返回的行并聚合它们
回答by Peter Cleary
I ran into a very similar problem with an Access subquery where the records were sorted by date. When I used the "Last" aggregate function I found it passed through all of the subqueries and retrieved the last row of data from the Access table, and not the sorted query as intended. Although I could have rewritten the query to use the aggregate function within the first set of parenthesis (as was previously suggested) I found it easier to save query results as a table in the database sorted in the order I wanted and then use the "Last" aggregate function to retrieve the values I wanted. I'll run an update query in the future to keep results current. Not efficient but effective.
我在 Access 子查询中遇到了一个非常相似的问题，其中记录按日期排序。当我使用“Last”聚合函数时，我发现它通过了所有子查询并从 Access 表中检索了最后一行数据，而不是按预期排序的查询。尽管我可以重写查询以在第一组括号内使用聚合函数（如先前建议的那样），但我发现将查询结果另存为数据库中按我想要的顺序排序的表更容易，然后使用“最后" 聚合函数来检索我想要的值。我将在未来运行更新查询以保持结果最新。效率不高，但有效。
