mysql数据库众数查询_mysql中位数、众数计算

平均数、中位数、众数常用来描述数据的集中程度，在mysql中，均值的计算较为简单，但中位数和众数尚不支持可以直接计算的函数，在这里向大家介绍一下中位数和众数的计算方法。

本文所使用的数据表为score，包含三个字段：

s_id 为学生id

c_id 为课程id

s_score 为课程成绩

1、中位数

查找课程编号为‘02’号的学生成绩中位数

课程编号为‘02’的课程成绩为

mysql> select s_score from score where c_id='02';

+---------+

| s_score |

+---------+

| 90 |

| 60 |

| 80 |

| 30 |

| 87 |

| 89 |

+---------+

6 rows in set (0.00 sec)

查找中位数

mysql> select avg(c.s_score) from(

-> select a.s_score from score a,score b

-> where a.c_id=b.c_id and a.c_id='02'

-> group by a.s_score

-> having sum(case when a.s_score=b.s_score then 1 else 0 end)

-> >= abs(sum(sign(a.s_score-b.s_score)))

-> )c;

+----------------+

| avg(c.s_score) |

+----------------+

| 83.5000 |

+----------------+

1 row in set (0.00 sec)

解析：

当把having条件作为查询内容时，便比较清晰了，结果如下

mysql> select a.s_score,

-> sum(case when a.s_score=b.s_score then 1 else 0 end )as if_equal,

-> abs(sum(sign(a.s_score-b.s_score)))

-> from score a,score b where a.c_id=b.c_id and a.c_id='02'

-> group by a.s_score;

+---------+----------+-------------------------------------+

| s_score | if_equal | abs(sum(sign(a.s_score-b.s_score))) |

+---------+----------+-------------------------------------+

| 30 | 1 | 5 |

| 60 | 1 | 3 |

| 80 | 1 | 1 |

| 87 | 1 | 1 |

| 89 | 1 | 3 |

| 90 | 1 | 5 |

+---------+----------+-------------------------------------+

6 rows in set (0.00 sec)

2、众数

一组数据可以存在多个众数

mysql> select s_score from score where c_id='02';

+---------+

| s_score |

+---------+

| 90 |

| 60 |

| 80 |

| 30 |

| 80 |

| 89 |

+---------+

6 rows in set (0.00 sec)

mysql> select s_score from score

-> where c_id='02'

-> group by s_score

-> having count(*)

-> >= all(select count(*) from score where c_id='02' group by s_score);

+---------+

| s_score |

+---------+

| 80 |

+---------+

1 row in set (0.00 sec)

解析：

mysql> select s_score,count(*) from score where c_id='02' group by s_score;

+---------+----------+

| s_score | count(*) |

+---------+----------+

| 30 | 1 |

| 60 | 1 |

| 80 | 2 |

| 89 | 1 |

| 90 | 1 |

+---------+----------+

5 rows in set (0.00 sec)

count() >= all(select count() from score where c_id='02' group by s_score)

表示score中分组后某个s_score出现的次数 >= 所有不同s_score出现的次数。