如何在SUM()+分组依据之后选择最大值? [英] 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个表:客户"和订单".

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和按客户分组"求和，然后从该表中选择具有订单总数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()+分组依据之后选择最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！