按频率对SQL查询记录进行排序 [英] Order SQL query records by frequency

问题描述

是否可以通过某种值在列中出现的频率来对您从SQL查询中选择的记录进行排序?例如:如果有五个记录，其中column ='value1'，三个记录column ='value2'，另外两个记录column ='value3'，则有一种方法可以使结果首先显示'value1'，然后显示'value2' ，最后是'value3'?

Is there any way to order the records you select from an SQL query by how often a certain value appears in a column? For example: If there are five records where column = 'value1', three where column = 'value2', and two where column = 'value3', is there a way to make the results display 'value1' first, then 'value2', and finally, 'value3'?

推荐答案

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)

这篇关于按频率对SQL查询记录进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！