SQL 如何在 SUM() + Group By 之后选择最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9441592/
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
How do I select the max value after a SUM() + Group By?
提问by nachoargentina
I have an SQL final exam in college in a few days and I have a query that's driving me crazy! I know it's a dumb query, but I'm just getting started and can't figure it out.
几天后我将在大学进行 SQL 期末考试，我有一个让我发疯的查询！我知道这是一个愚蠢的查询，但我才刚刚开始，无法弄清楚。
So, there's basicaly 2 tables, Client and Orders.
所以，基本上有 2 个表，客户和订单。
Client Orders
--------- ---------
PK Client_Id PK Order_Id
Name Client_Id FK
Order_Total
Date
Now, they ask me to "List the name of the client that bought the most in 2011"
现在，他们让我“列出 2011 年购买最多的客户的名字”
So, for what I thought, this requires on one side, that I SUM all the Order_Total and Group by Client from 2011, then from that table, select the client with the MAX() sum of order totals, and then show only the name of that client. The problem is that I can't figure how to put all that in one query.
因此，对于我的想法，这需要一方面，我将 2011 年的所有 Order_Total 和 Group by Client 相加，然后从该表中，选择具有 MAX() 订单总数总和的客户，然后仅显示名称那个客户。问题是我不知道如何将所有这些放在一个查询中。
Hope somebody can help!
希望有人能帮忙！
Thank you all for your very quick responses! I'm really impressed!
感谢大家的快速回复！我真的很感动！
Now, I don't mean to be picky or anything, but just in case my teacher doesn't accept the "Limit" or "Select top" statement, is there any way to do this query without those?
现在，我并不是要挑剔或任何东西，但以防万一我的老师不接受“限制”或“选择顶部”语句，有没有办法在没有这些的情况下执行此查询？
Edit: Original code attempt ported from comments:
编辑：从评论移植的原始代码尝试：
SELECT
C.NAME
FROM
CLIENTS C,
ORDERS O
WHERE
O.CLIENT_ID = C.CLIENT_ID
AND O.DATE BETWEEN '1/1/2011 00:00:00.000' and '12/31/2011 23:59:59.999'
HAVING SUM(O.ORDER_TOTAL) >= ALL (SELECT SUM (O2.ORDER_TOTAL) FROM ORDER O2 GROUP BY O2.CLIENT_ID)
采纳答案by Isaac
SELECT T.X
(SELECT C.NAME X, SUM(O.ORDER_TOTAL)
FROM CLIENT C, ORDERS O
WHERE C.CLIENT_ID = O.CLIENT_ID
AND YEAR(O.DATE) = 2011
GROUP BY O.CLIENT_ID
ORDER BY 2 DESC
LIMIT 1) T;
回答by Fosco
There are many ways to skin this cat... this is how I'd normally do it:
有很多方法可以给这只猫剥皮……这是我通常的做法：
select top 1 c.Client_Id, c.Name, o.Order_Total
from Client c
join (select Client_Id, SUM(Order_Total) as Order_Total
from Orders
where `Date` between '1/1/2011 00:00:00.000' and '12/31/2011 23:59:59.999'
group by Client_Id) o
on c.Client_Id = o.Client_Id
order by o.Order_Total desc
Basically you're pulling the order total list, joining against that, sorting it descending by order total, and limiting the query to 1 result.
基本上，您正在拉取订单总数列表，加入该列表，按订单总数降序对其进行排序，并将查询限制为 1 个结果。
回答by akousmata
Is7aq's answer is correct according to your question but will only work on MySQL. Nor does it take into account the possibility that more than one client bought the most in the given year although I admit that wasn't a constraint listed in the original question. Also it's a huge performance hit to cross joins by simply using commas once your databases get sufficiently large so it's almost always better to use INNER or OUTER joins and specify the condition of the join. Anyways, did this as an exercise and here's what I had. It's could probably be better optimized:
根据您的问题，Is7aq 的答案是正确的，但仅适用于 MySQL。它也没有考虑到在给定的一年中不止一个客户购买最多的可能性，尽管我承认这不是原始问题中列出的限制条件。此外，一旦您的数据库变得足够大，通过简单地使用逗号来交叉连接也会对性能造成巨大影响，因此使用 INNER 或 OUTER 连接并指定连接条件几乎总是更好。无论如何，这是作为练习而做的，这就是我所拥有的。它可能可以更好地优化：
CREATE TABLE #Client (
Client_Id int not null,
Name varchar(100) not null
)
INSERT INTO #Client VALUES (1, 'Client 1')
INSERT INTO #Client VALUES (2, 'Client 2')
INSERT INTO #Client VALUES (3, 'Client 3')
CREATE TABLE #Orders (
Order_Id int not null,
Client_Id int not null,
Order_Total int not null,
Date datetime not null
)
-- Customer 1: total=105
INSERT INTO #Orders VALUES (1, 1, 55, '1/1/2011')
INSERT INTO #Orders VALUES (2, 1, 50, '1/1/2011')
INSERT INTO #Orders VALUES (3, 1, 45, '1/1/2010') -- test removal of invalid dates
-- Customer 2: total=120
INSERT INTO #Orders VALUES (4, 2, 40, '1/1/2011')
INSERT INTO #Orders VALUES (5, 2, 40, '1/1/2011')
INSERT INTO #Orders VALUES (6, 2, 40, '1/1/2011')
-- Customer 3: total=120
INSERT INTO #Orders VALUES (7, 3, 40, '1/1/2011')
INSERT INTO #Orders VALUES (8, 3, 40, '1/1/2011')
INSERT INTO #Orders VALUES (9, 3, 40, '1/1/2011')
-- control customer to test hi/lo scenarios: total=40
INSERT INTO #Orders VALUES (10, 4, 10, '1/1/2011')
INSERT INTO #Orders VALUES (11, 4, 10, '1/1/2011')
INSERT INTO #Orders VALUES (12, 4, 10, '1/1/2011')
INSERT INTO #Orders VALUES (13, 4, 10, '1/1/2011')
SELECT T.NAME,
T.OrderTotal
FROM (SELECT C.NAME,
SUM(O.ORDER_TOTAL) OrderTotal
FROM #CLIENT C
INNER JOIN #ORDERS O
ON c.CLIENT_ID = o.CLIENT_ID
WHERE YEAR(O.DATE) = 2011
GROUP BY C.NAME) as T
WHERE T.OrderTotal = (SELECT MAX(T2.OrderTotal2)
FROM (SELECT C2.NAME,
SUM(O2.ORDER_TOTAL) OrderTotal2
FROM #CLIENT C2
INNER JOIN #ORDERS O2
ON c2.CLIENT_ID = o2.CLIENT_ID
WHERE YEAR(O2.DATE) = 2011
GROUP BY C2.NAME) as T2)
GROUP BY T.Name, T.OrderTotal
DROP TABLE #Client
DROP TABLE #Orders
-- Output
-- Client 2: 120
-- Client 3: 120
回答by Luiz Alfredo Galiza
SELECT Client.Name
FROM Client LEFT JOIN Orders ON Orders.Client_Id = Client.Client_Id
WHERE YEAR(Orders.Date) = 2011
GROUP BY Client.Client_Id
ORDER BY SUM(Order.Order_Total) DESC
LIMIT 1
回答by Derek Kromm
You're almost there, you just need to select from your aggregate query. It's called a derived table.
您就快完成了，您只需要从聚合查询中进行选择。它被称为派生表。
So you have this:
所以你有这个：
select c.client_id, c.name, sum(order_total) ordersum
from client c
inner join orders o on c.client_id = o.client_id
where year(o.date) = 2011
group by c.client_id, c.name
That gives you your sums. Now you want the first one. There are several ways to do this and it's also dependent on what DBMS you're using (mysql, mssql, etc.) The easiest method, though, is this:
这给你你的总和。现在你想要第一个。有几种方法可以做到这一点，它还取决于您使用的 DBMS（mysql、mssql 等）。不过，最简单的方法是：
select top 1 *
from (
select c.client_id, c.name, sum(order_total) ordersum
from client c
inner join orders o on c.client_id = o.client_id
where year(o.date) = 2011
group by c.client_id, c.name
) a
order by ordersum desc
There may be something simpler on a given platform, but that query should be generic enough to work on whatever DBMS you're using.
在给定的平台上可能有一些更简单的东西，但该查询应该足够通用，可以在您使用的任何 DBMS 上工作。
