SELECT column
, COUNT(column
) AS count
FROM table
GROUP BY column
ORDER BY count
DESC
快速PoC:
MysqL> CREATE TABLE `table` (`id` SERIAL, `column` char(6) NOT NULL, KEY `column_idx`(`column`));
Query OK, 0 rows affected (0.01 sec)
MysqL> INSERT INTO `table` (`column`) VALUES ('value1'), ('value1'), ('value1'), ('value1'), ('value1'), ('value2'), ('value2'), ('value2'), ('value3'), ('value3');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
MysqL> SELECT * FROM `table`;
+----+--------+
| id | column |
+----+--------+
| 1 | value1 |
| 2 | value1 |
| 3 | value1 |
| 4 | value1 |
| 5 | value1 |
| 6 | value2 |
| 7 | value2 |
| 8 | value2 |
| 9 | value3 |
| 10 | value3 |
+----+--------+
10 rows in set (0.00 sec)
MysqL> SELECT `column`,
-> COUNT(`column`) AS `count`
-> FROM `table`
-> GROUP BY `column`
-> ORDER BY `count` DESC;
+--------+-------+
| column | count |
+--------+-------+
| value1 | 5 |
| value2 | 3 |
| value3 | 2 |
+--------+-------+
3 rows in set (0.00 sec)