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

Question

嗨，我有以下代码，我试图获取飞机 ID (aircraftid) 以及它们已维修的次数 (count(serviceid))，但仅在 count(serviceid) 大于时才显示它们平均计数(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;

它返回行

Answer 1

你可以试试这个-

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
)