本文介绍了按频率对SQL查询记录进行排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以通过某种值在列中出现的频率来对您从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查询记录进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!