返回高于平均值的行
返回高于平均值的行
提问于 2017-07-17 15:48:20
我试图找出订单数量高于平均水平的国家。这就是我所得到的。问题是(见图)，每个国家的订单数量都是不计算的，因为每个国家的订单都应该不同。
SELECT avg(NumberOrders) as avg,
Customers.Country,
NumberOrders
FROM Customers,
(SELECT COUNT(Orders.OrderId) AS NumberOrders
FROM Customers JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country) nested
GROUP BY Customers.Country
HAVING NumberOrders > avg;
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-07-17 15:58:15
如果数据库管理系统支持窗口聚集(除了MySQL和Access之外，几乎所有数据库都支持)：
select *
from
(
SELECT Customers.Country,
COUNT(Orders.OrderId) AS NumberOrders, -- count per country
AVG(COUNT(Orders.OrderId)) OVER () AS avgOrders -- average count
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
) nested
WHERE NumberOrders > avgOrders编辑：
对于不支持窗口集合的DBMSes，它要复杂得多：
SELECT Customers.Country,
COUNT(Orders.OrderId) AS NumberOrders -- count per country
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
HAVING COUNT(Orders.OrderId) >
( select avg(NumberOrders)
from
(
SELECT Customers.Country,
COUNT(Orders.OrderId) AS NumberOrders -- count per country
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
) AS dt
)如果DBMS支持公共表表达式，则可以简化如下：
with cte as
(
SELECT Customers.Country,
COUNT(Orders.OrderId) AS NumberOrders -- count per country
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
)
select *
from cte
WHERE NumberOrders >
(
select avg(NumberOrders) from cte
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45148506
复制相关文章
相似问题
腾讯云开发者
