MySQL查询以排除最高计数和最低计数

Question

我在MySql中有下表

Number
1
2
3
2
3
4
4
4

我想按数字计算，但不包括最高数和最低数。这应该是结果

Number  Count
2       2
3       2

请帮我处理这个问题。

Answer 1

试试这个：

SELECT number, COUNT(*) cnt
FROM mytable
GROUP BY number
HAVING cnt <> (SELECT COUNT(*)
               FROM mytable
               GROUP BY number
               ORDER BY COUNT(*) LIMIT 1)
       AND               

       cnt <> (SELECT COUNT(*)
               FROM mytable
               GROUP BY number
               ORDER BY COUNT(*) DESC LIMIT 1)

HAVING子句中使用的两个子查询返回最小计数和最大计数。因此，HAVING子句的两个谓词过滤掉了计数最高和最低的组。

Answer 2

一个解决方案是使用user variable和sub-query。

所需查询：

select Number, Count
from (
    select Number, count(*) as Count, @rank := @rank+1 as rank
    from t1 cross join (select @rank := 0) param
    group by Number
    order by Count ) t2 
where rank not in (1, @rank);

演示：

create table t1(Number int);
insert into t1 values(1),(2),(3),(2),(3),(4),(4),(4);

select Number, Count
from (
    select Number, count(*) as Count, @rank := @rank+1 as rank
    from t1 cross join (select @rank := 0) param
    group by Number
    order by Count ) t2 
where rank not in (1, @rank);

输出：

mysql> select Number, Count
    -> from (
    -> select Number, count(*) as Count, @rank := @rank+1 as rank
    -> from t1 cross join (select @rank := 0) param
    -> group by Number
    -> order by Count ) t2
    -> where rank not in (1, @rank);
+--------+-------+
| Number | Count |
+--------+-------+
|      2 |     2 |
|      3 |     2 |
+--------+-------+
2 rows in set (0.00 sec)

Answer 3

select number,count(number)
from numbers 
where number not in ((select max(number) from numbers),(select min(number) from numbers)  )
group by number

方形小提琴演示