SQL oracle中count的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25857328/
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 value of count in oracle
提问by WaelT
I have these tables, Orders Table:
我有这些表，订单表：
Name Null? Type
ORDER_ID NOT NULL NUMBER(5)
CUSTOMER_ID NUMBER(8)
SHIPMENT_METHOD_ID NUMBER(2)
and Shipment_method Table:
和 Shipment_method 表：
Name Null? Type
SHIPMENT_METHOD_ID NOT NULL NUMBER(2)
SHIPMENT_DESCRIPTION VARCHAR2(80)
I'm trying to get the most used shipping method based on the orders, and I'm kind of a beginner here so I need some help. I'm thinking if it's possible to have MAX(count(order_id)) but how can I do that for each shipment_method_id?
我正在尝试根据订单获得最常用的运输方式，而且我是这里的初学者，所以我需要一些帮助。我在想是否有可能有 MAX(count(order_id)) 但我该如何为每个shipment_method_id 做到这一点？
回答by Brian DeMilia
This is another approach:
这是另一种方法：
select shipment_method_id, shipment_description, count(*) as num_orders
from orders
join shipment_method
using (shipment_method_id)
group by shipment_method_id, shipment_description
having count(*) = (select max(count(order_id))
from orders
group by shipment_method_id)
回答by Barmar
You don't need MAX, you just need to return the top row
你不需要MAX，你只需要返回顶行
SELECT Shipment_Method_Desc
FROM (
SELECT Shipment_Method_ID, Shipment_Method_Desc, COUNT(*) AS ct
FROM Shipment_Method s
JOIN Orders o ON s.Shipment_Method_ID = o.Shipment_Method_ID
GROUP BY Shipment_Method_ID
ORDER BY ct DESC)
WHERE ROWNUM = 1
If you're using Oracle 12c or newer, you can use the row limiting clauseinstead of the subquery:
如果您使用的是 Oracle 12c 或更新版本，则可以使用行限制子句而不是子查询：
SELECT Shipment_Method_ID, Shipment_Method_Desc, COUNT(*) AS ct
FROM Shipment_Method s
JOIN Orders o ON s.Shipment_Method_ID = o.Shipment_Method_ID
GROUP BY Shipment_Method_ID
ORDER BY ct DESC
FETCH FIRST 1 ROW ONLY
回答by DrabJay
Here is a method that allows for more than one Shipment Method having the same maximum number of Orders.
这是一种允许多个具有相同最大订单数的发货方法的方法。
SELECT shipment_method_id
,shipment_description
,orders
FROM
(SELECT shipment_method_id
,shipment_description
,orders
,rank() OVER (ORDER BY orders DESC) orders_rank
FROM
(SELECT smm.shipment_method_id
,smm.shipment_description
,count(*) orders
FROM orders odr
INNER JOIN shipment_method smm
ON (smm.shipment_method_id = odr.shipment_method_id)
GROUP BY smm.shipment_method_id
,smm.shipment_description
)
)
WHERE orders_rank = 1
回答by halfbit
As a beginner, you may find using withuseful which allows to have kind of named intermediate results:
作为初学者，您可能会发现 usingwith允许具有某种命名的中间结果：
with STATS as (select SHIPMENT_METHOD_ID, count(*) as N
from ORDERS group by SHIPMENT_METHOD_ID)
, MAXIMUM as (select max(N) as N from STATS)
select SHIPMENT_METHOD_ID, SHIPMENT_DESCRIPTION
from STATS
join MAXIMUM on STATS.N = MAXIMUM.N
natural join SHIPMENT_METHOD
