mysql - 以下 SQL 查询的正确解决方案是什么？

Question

Print name of all activities with neither maximum nor minimum number of participants

我尝试了以下查询，但出现错误:

select ACTIVITY
from (select ACTIVITY, count(*) as cnt,
             max(count(*)) as max_cnt,
             min(count(*)) as min_cnt
      from FRIENDS GROUP BY ACTIVITY) FRIENDS
where cnt not in (max_cnt, min_cnt);

ERROR: ERROR 1111 (HY000) at line 1: Invalid use of group function MYSQL VERSION: 8

Answer 1

据推测，您没有运行 MySQL 8.0(否则您在上一个问题中给出的答案会起作用)。

在早期版本中，您可以:

select activity, count(*) no_activities
from friends
group by activity
having 
        count(*) > (select count(*) from friends group by activity order by count(*)  asc limit 1)
    and count(*) < (select count(*) from friends group by activity order by count(*) desc limit 1)