按频率排序SQL查询记录

问题描述：

有什么方法可以通过某个值出现在列中的频率来排序从SQL查询中选择的记录吗？例如：如果有五个记录，其中column ='value1'，其中column ='value2'，两个where column ='value3'，那么有办法使结果首先显示'value1'，然后'value2' ，最后是'value3'？按频率排序SQL查询记录

在你的例子中，你希望最终结果中的* 3 *行（按count排序不同的“列”值）还是* 10 *行（所有的“列”值按值频率排序）？ – pilcrow

答

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)

答

假设你想所有行，但值的一些列`col`的频率排序，你可以这样做：

CREATE TABLE tbl (id SERIAL, col VARCHAR(16)); 
-- INSERT so that `id` does not match frequency of values under `col` 
INSERT INTO tbl (col) VALUES ('value1'), ('value2'), ('value3'), 
          ('value1'), ('value2'), ('value3'), 
          ('value1'), ('value2'), 
          ('value1'), 
          ('value1'); 

    SELECT id, tbl.col 
     FROM tbl 
INNER JOIN ( SELECT col, COUNT(1) AS freq 
       FROM tbl 
      GROUP BY 1) derived 
      USING (col) 
    ORDER BY derived.freq DESC;

这将生产

+----+--------+ 
| id | col | 
+----+--------+ 
| 4 | value1 | <-- highest incidence 
| 7 | value1 | 
| 1 | value1 | 
| 9 | value1 | 
| 10 | value1 | 
| 5 | value2 | 
| 8 | value2 | 
| 2 | value2 | 
| 6 | value3 | <-- lowest incidence 
| 3 | value3 | 
+----+--------+ 
10 rows in set (0.00 sec)

按频率排序SQL查询记录

相关推荐