MySQL 按出现列数排序

Question

我有一张这样的 table

|   id  |   name    |
----------------------
|   A   |   test a1 |
|   A   |   test a2 |
|   A   |   test a3 |
|   B   |   test b1 |
|   B   |   test b2 |
|   A   |   test a4 |
|   C   |   test c1 |

Id A 具有最多的值，然后是 B 等。如何返回按与 id 关联的名称数量排序的行>。我试过了，但是因为我按 id 分组，所以我丢失了与 id 关联的所有 names。

SELECT id, name, COUNT(name) as name_count 
FROM users 
GROUP BY id 
ORDER BY name_count DESC

Answer 1

你很接近。为了避免丢失名称，您需要加入一个子查询来处理计数:

SELECT u.id, u.name
FROM users u
JOIN (
  SELECT id, count(1) cnt
  FROM users
  GROUP BY id) ss 
ON u.id = ss.id
ORDER BY ss.cnt DESC

子查询结果为this

+----+-----+
| id | cnt |
+----+-----+
| A  |   4 |
| B  |   2 |
| C  |   1 |
+----+-----+

加入到您的原始表中，我们有这些行:

+------+---------+-------+--------+
| u.id | u.name  | ss.id | ss.cnt |
+------+---------+-------+--------+
| A    | test a1 | A     |      4 |
| A    | test a2 | A     |      4 |
| A    | test a3 | A     |      4 |
| B    | test b1 | B     |      2 |
| B    | test b2 | B     |      2 |
| A    | test a4 | A     |      4 |
| C    | test c1 | C     |      1 |
+------+---------+-------+--------+

所以我们只想返回 u users 表中的列并按子查询的 ss.cnt 排序。您可以选择 u.*，但通常更好的做法是在您希望查询返回的列中明确显示。

SQLFiddle

如果您想在 ID 组内对结果进行排序，您还可以添加基于 u.name 的排序:

ORDER BY ss.cnt DESC, u.name

SQLFiddle