mysql - 如何将每个组的结果与SQL中的总体跨组指标进行比较

我的问题是，由于试图从GROUP BY结果中获取平均值，我无法弄清楚the 10th question。我的错误代码如下

SELECT tc.staff,SUM(ev.duration)
FROM
teaches tc
JOIN
event ev
ON
tc.event = ev.id
GROUP BY tc.staff
HAVING SUM(ev.duration)>AVG(SUM(ev.duration))

最佳答案

SELECT count(rr.staff)
FROM (
    SELECT  tc.staff,
        SUM(ev.duration) as 'total'
    FROM
            teaches tc,
            staff st,
            event ev
    WHERE   st.id = tc.staff
    AND tc.event = ev.id
    GROUP BY tc.staff
    HAVING total > (SELECT AVG(ss.total) as 'average'
            FROM  (SELECT tc.staff,
                            SUM(ev.duration) as 'total'
                    FROM
                            teaches tc,
                            staff st,
                            event ev
                    WHERE   st.id = tc.staff
                    AND tc.event = ev.id
                    GROUP BY tc.staff) ss)) rr;

关于mysql - 如何将每个组的结果与SQL中的总体跨组指标进行比较，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26707041/