MySQL-聚合函数的滥用

Question

我一直在尝试运行以下命令:

select s.name, s.nr
from sub s
group by s.name
having (select count(s.name) from sub s group by s.name) > 
10 * (select avg(count(s.name)) from sub s group by s.name)

为了获取所有调用电话次数超过每个订阅者平均调用次数 10 倍的订阅者的姓名和电话号码。

我在 select avg(count(s.name)) from sub s group by s.name 中做错了什么？

Answer 1

可能是这样的:

select s.name, s.nr
from sub s
group by s.name, s.nr
having count(*) > 10 * (
    select avg(cnt)
    from (
        select count(*) as cnt
        from sub
        group by name, nr
     )x

您还可以使用单个(非嵌套)子查询获取平均计数:

select count(*) / count(distinct name, nr) from sub

将其解读为:所有行数/不同组数 - 这是每组的平均行数。

所以您的完整查询将是:

select s.name, s.nr
from sub s
group by s.name, s.nr
having count(*) > 10 * (select count(*) / count(distinct name, nr) from sub)