你很接近。但是 NOT IN 确实是这样的——因为子查询返回多个列。你只比较了一个值。相反，使用两个单独的比较：

SELECT activity
FROM a
WHERE n <> (SELECT MAX(n) FROM a) AND
      n <> (SELECT MIN(n) FROM a) ;

我猜是因为 SELECT MAX(n), MIN(n) FROM a 将生成两列，而不是两行。
是的，这就是重点。除了使用两个子查询（您自己已经找到了），您还可以利用这里的窗口函数（事实上您使用 with 子句表示您正在运行mysql 8.0，它支持窗口函数）：

select activity
from (
    select 
        activity, 
        row_number() over(order by count(*) asc)  rn_asc,
        row_number() over(order by count(*) desc) rn_desc
    from friends
    group by activity
) t
where 1 not in (rn_asc, rn_desc)

我怀疑这比 with 子句和两个子查询。

而不是在中使用子查询 WHERE ，可以与子查询联接。

WITH a AS(
    SELECT activity, COUNT(1) AS n 
    FROM Friends
    GROUP BY activity
    )

SELECT activity
FROM a AS a1
JOIN (SELECT MAX(n) AS maxn, MIN(n) AS minn) AS a2
ON a1.n NOT IN (a2.maxn, a2.minn)

你可以用 MIN() 以及 MAX() 窗口功能：

WITH cte AS (
  SELECT activity, 
         COUNT(*) AS n,
         MIN(COUNT(*)) OVER () min_n,
         MAX(COUNT(*)) OVER () max_n          
  FROM Friends
  GROUP BY activity
)
SELECT activity, n
FROM cte
WHERE n NOT IN (min_n, max_n)

请看演示。
结果：

| activity | n   |
| -------- | --- |
| Singing  | 2   |