php - 按频率对 SQL 查询记录排序

Question

有什么方法可以根据某个值在列中出现的频率来对您从 SQL 查询中选择的记录进行排序吗？例如:如果有五个记录 where column = 'value1'，三个 where column = 'value2'，两个 where column = 'value3'，有没有办法让结果先显示 'value1'，然后显示 'value2' , 最后是 'value3'?

Answer 1

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: 0mysql> 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)