找到发送订单最多的城市将导致分配城市和订单数量(命名金额列(。我有两张表——命名的客户和订单
SELECT Customers.City,count( Orders.OrderID) as amount
FROM voodoo.Customers
inner join voodoo.Orders on Customers.CustomerID=Orders.CustomerID
group by Customers.City
having amount >= all(select count(Orders.OrderID)
from voodoo.Customers
inner join voodoo.Orders on Customers.CustomerID=Orders.CustomerID
group by Customers.City);
表格
您不需要子查询,因为您只需按金额(降序(排序,并将结果限制为1:
SELECT Customers.City, count(Orders.OrderID) as amount
FROM voodoo.Customers INNER JOIN voodoo.Orders
ON Customers.CustomerID=Orders.CustomerID
GROUP BY Orders.OrderID
ORDER BY amount DESC
LIMIT 1;
编者按:正如Thorsten Kettner所指出的,我做了一份&粘贴错误;正确的版本将是CCD_ 1。
您要查找的是每个城市的订单数,而不是每个订单的订单数。所以,不要按顺序分组,而是按城市分组。对于城市排名,您可以使用RANK
或DENSE_RANK
。
SELECT city, amount
FROM
(
SELECT
c.city,
COUNT(o.orderid) AS amount,
RANK() OVER (ORDER BY COUNT(o.orderid) DESC) AS rnk
FROM voodoo.customers c
INNER JOIN voodoo.orders o ON o.customerid = c.customerid
group by c.city
) counted_and_ranked
WHERE rnk = 1;