SQL 中总和的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/260398/
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
Max of Sum in SQL
提问by
I have a list of stores, departments within the stores, and sales for each department, like so (created using max(sales) in a subquery, but that's not terribly important here I don't think):
我有一个商店、商店内部门和每个部门的销售额的列表，就像这样（在子查询中使用 max(sales) 创建，但我认为这在这里并不是非常重要）：
toronto baskets 500
vancouver baskets 350
halifax baskets 100
toronto noodles 275
vancouver noodles 390
halifax noodles 120
halifax fish 200
I would like to ask for the highest-selling department at each store. The results should look like this:
我想询问每家商店最畅销的部门。结果应如下所示：
toronto baskets 500
vancouver noodles 275
halifax fish 200
Whenever I use GROUP BY, it includes all the listings from my subquery. Is there a nice clean way to do this without a temporary table?
每当我使用 GROUP BY 时，它都会包含我的子查询中的所有列表。在没有临时表的情况下，有没有一种很好的干净的方法来做到这一点？
回答by Pete
This works in Sql Server (2000 and above for sure)
这适用于 Sql Server（肯定是 2000 及以上）
SELECT a.Store, a.Department, a.Sales
FROM temp a
INNER JOIN
(SELECT store, max(sales) as sales
FROM temp
GROUP BY Store) b
ON a.Store = b.Store AND a.Sales = b.Sales;
回答by Robert Wagner
My 2 solutions for SQL 2005 is below. The other ones I can see so far may not return the correct data if two of the sales figures are the same. That depends on your needs though.
我的 SQL 2005 的 2 个解决方案如下。如果两个销售数据相同，我目前看到的其他数据可能无法返回正确的数据。不过，这取决于您的需求。
The first uses the Row_Number() function, all the rows are ranked from the lowest to the highest sales (then some tie breaking rules). Then the highest rank is chosen per store to get the result.
第一个使用 Row_Number() 函数，所有行按照销售额从低到高排列（然后是一些打破平局的规则）。然后每个商店选择最高排名以获得结果。
You can try adding a Partion By clause to the Row_Number function (see BOL) and/or investigate using a inner join instead of an "in" clause.
您可以尝试将 Partion By 子句添加到 Row_Number 函数（请参阅 BOL）和/或使用内部联接而不是“in”子句进行调查。
The second, borrowing on Turnkey's idea, again ranks them, but partitions by store, so we can choose the first ranked one. Dense_Rank will possibly give two identical rows the same rank, so if store and department were not unique, it could return two rows. With Row_number the number is unique in the partition.
第二个，借用Turnkey的想法，再次对它们进行排名，但按商店划分，因此我们可以选择排名第一的。Dense_Rank 可能会给两个相同的行赋予相同的等级，因此如果商店和部门不是唯一的，它可以返回两行。使用 Row_number，该编号在分区中是唯一的。
Some things to be aware of is that this may be slow, but would be faster for most data sets than the sub-query in one of the other solutions. In that solution, the query would have to be run once per row (including sorting etc), which could result in a lot of queries.
需要注意的一些事情是，这可能会很慢，但对于大多数数据集来说，它会比其他解决方案之一中的子查询更快。在该解决方案中，查询必须每行运行一次（包括排序等），这可能会导致大量查询。
Other queries the select the max sales per store and return the data that way, return duplicate rows for a store if two departments happen to have the same sales. The last query shows this.
其他查询选择每个商店的最大销售额并以这种方式返回数据，如果两个部门碰巧有相同的销售额，则返回商店的重复行。最后一个查询显示了这一点。
DECLARE @tbl as TABLE (store varchar(20), department varchar(20), sales int)
INSERT INTO @tbl VALUES ('Toronto', 'Baskets', 500)
INSERT INTO @tbl VALUES ('Toronto', 'Noodles', 500)
INSERT INTO @tbl VALUES ('Toronto', 'Fish', 300)
INSERT INTO @tbl VALUES ('Halifax', 'Fish', 300)
INSERT INTO @tbl VALUES ('Halifax', 'Baskets', 200)
-- Expect Toronto/Noodles/500 and Halifax/Fish/300
;WITH ranked AS -- Rank the rows by sales from 1 to x
(
SELECT
ROW_NUMBER() OVER (ORDER BY sales, store, department) as 'rank',
store, department, sales
FROM @tbl
)
SELECT store, department, sales
FROM ranked
WHERE rank in (
SELECT max(rank) -- chose the highest ranked per store
FROM ranked
GROUP BY store
)
-- Another way
SELECT store, department, sales
FROM (
SELECT
DENSE_RANK() OVER (PARTITION BY store ORDER BY sales desc,
store desc, department desc) as 'rank',
store, department, sales
FROM @tbl
) tbl
WHERE rank = 1
-- This will bring back 2 rows for Toronto
select tbl.store, department, sales
from @tbl tbl
join (
select store, max(sales) as maxSales from @tbl group by store
) tempTable on tempTable.store = tbl.store
and tempTable.maxSales = tbl.sales
回答by Noah Yetter
This works in Oracle, other implementations may have different syntax for analytic functions (or lack them entirely):
这在 Oracle 中有效，其他实现可能对分析函数有不同的语法（或完全没有它们）：
select store
, max(department) keep(dense_rank last order by sales)
, max(sales)
from (
...query that generates your results...
)
group by store
回答by Jeffrey Meyer
This will work in SQL Server, as of 2005:
自 2005 年起，这将适用于 SQL Server：
with data as
(select store, department, sales
from <your query>),
maxsales as
(select store, sales = max(sales)
from data
group by store)
select store, (select top 1 department from data where store = t.store and sales = t.sales order by [your criteria for ties]), sales
from maxsales m
I'm assuming you only want to display 1 department in the event of ties, hence the top 1 and [your criteria for ties] to distinguish among them.
我假设您只想在出现关系时显示 1 个部门，因此前 1 个部门和 [您的关系标准] 以区分它们。
回答by Rockcoder
Maybe this could work. Haven't tried it though, there could be a better solution...
也许这可以奏效。不过没试过，可能有更好的解决方案......
select yourTable.store, dept, sales
from yourTable
join (
select store, max(sales) as maxSales from yourTable group by store
) tempTable on tempTable.store = yourTable.store
and tempTable.maxSales = yourTable.sales
回答by Turnkey
This will work in SQL Server without temp tables:
这将在没有临时表的 SQL Server 中工作：
SELECT Store, Department, Sales FROM
(SELECT Store, Department, Sales,
DENSE_RANK() OVER (PARTITION BY Store
ORDER BY Sales DESC) AS Dense_Rank
FROM Sales) A WHERE Dense_Rank = 1
WHERE "Sales" = your original query
WHERE“销售”=您的原始查询
回答by Charles Bretana
This will work
这将工作
Select Store, Department, Sales
From yourTable A
Where Sales = (Select Max(Sales)
From YourTable
Where Store = A.Store)
