mysql 查询一张表某几个字段 数据量重复次数最多的一条数据

下面这SQL满足你的要求，我在本地测试过，可以拿过去直接用：

mysql> select * from face;
+----+-----+------+-------+---------------------+
| id | eye | nose | mouth | create_time         |
+----+-----+------+-------+---------------------+
|  1 | a1  | b1   | c1    | 2015-04-07 20:40:11 |
|  2 | a1  | b2   | c3    | 2015-04-07 20:40:28 |
|  3 | a2  | b2   | c1    | 2015-04-07 20:40:52 |
|  4 | a2  | b3   | c2    | 2015-04-07 20:41:03 |
|  5 | a3  | b4   | c4    | 2015-04-07 20:41:19 |
+----+-----+------+-------+---------------------+
5 rows in set (0.00 sec)
 
mysql> select eye.eye, nose.nose, mouth.mouth from (select eye, create_time, count(*) from face group by eye order by count(*) desc, max(create_time) desc limit 1) as eye, (select nose, create_time, count(*) from face group by nose order by count(*) desc, max(create_time) desc limit 1) as nose, (select mouth, create_time, count(*) from face group by mouth order by count(*) desc, max(create_time) desc limit 1) as mouth;
+-----+------+-------+
| eye | nose | mouth |
+-----+------+-------+
| a2  | b2   | c1    |
+-----+------+-------+
1 row in set (0.01 sec)

看了下一楼回复的子查询，在处理数量一样，选时间最新的上有点小问题。这就是上面我加入max(create_time)的原因。不用max，返回的时间都是第一条记录的。处理不了这种情况：a2第二条记录的时间大于a1两条记录的时间。但是a1第一条记录的时间大于a2第一条记录的时间。

注意返回时间：

有max得情况：

mysql> select eye, create_time, count(*) from face group by eye order by count(*) desc, max(create_time) desc limit 1;
+-----+---------------------+----------+
| eye | max(create_time)    | count(*) |
+-----+---------------------+----------+
| a2  | 2015-04-07 20:41:03 |        2 |
+-----+---------------------+----------+
1 row in set (0.00 sec)

没有max的情况：

mysql> select eye, create_time, count(*) from face group by eye order by count(*) desc, create_time desc limit 1;
+-----+---------------------+----------+
| eye | create_time         | count(*) |
+-----+---------------------+----------+
| a2  | 2015-04-07 20:40:52 |        2 |
+-----+---------------------+----------+
1 row in set (0.00 sec)

注：max放在order by 后面。