mysql - 获取子查询SQL的平均值

嗨，我有以下代码，我在这里尝试获取飞机ID（aircraftid）及其服务的次数（count（serviceid）），但仅在count（serviceid）大于平均数量的情况下显示它们count（serviceid）。

select aircraftid, s.times
from(
select aircraftid, count(serviceid) as times
from service
group by aircraftid
) as s
having times > avg(times);

但是当我运行它时，它不返回任何行

当我将代码更改为

select aircraftid, s.times
from(
select aircraftid, count(serviceid) as times
from service
group by aircraftid
) as s
having times > 2;

它返回行

最佳答案

您可以尝试-

SELECT aircraftid,
COUNT(serviceid) AS times
FROM service
GROUP BY aircraftid
HAVING COUNT(serviceid) > (
    SELECT AVG(T) FROM
    (
        SELECT count(serviceid) T
        FROM service
        GROUP BY aircraftid
    )A
)

关于mysql - 获取子查询SQL的平均值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58571800/

aircraftID

mysql - 获取子查询SQL的平均值