如何在 SUM() + Group By 之后选择最大值? [英] How do I select the max value after a SUM() + Group By?

问题描述

几天后我将在大学进行一次 SQL 期末考试，我有一个让我发疯的查询！我知道这是一个愚蠢的查询，但我才刚刚开始，无法弄清楚.

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.

所以，基本上有 2 个表，Client 和 Orders.

So, there's basicaly 2 tables, Client and Orders.

     Client           Orders
     ---------        ---------
 PK  Client_Id    PK  Order_Id
     Name             Client_Id   FK
                      Order_Total
                      Date

现在，他们让我列出 2011 年购买最多的客户的姓名"

所以，就我的想法而言，一方面，我需要对 2011 年以来的所有 Order_Total 和 Group by Client 进行求和，然后从该表中选择具有 MAX() 总和的客户，然后显示只有那个客户的名字.问题是我不知道如何将所有这些都放在一个查询中.

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.

希望有人能帮忙！

感谢大家的快速回复！我真的很感动！

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?

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)

推荐答案

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;

这篇关于如何在 SUM() + Group By 之后选择最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！