如何按分组基数排序并展示GROUP BY分组后的所有原始行数据

你遇到的问题很典型——想要按每个用户的行数（分组基数）排序，同时保留所有原始数据行，普通的ORDER BY和GROUP BY确实满足不了需求。这里有两种可行的方案，适配不同的SQL环境：

方案一：使用窗口函数（推荐，现代SQL数据库支持） #

窗口函数可以在不聚合行的前提下，为每一行计算所属分组的总行数，完美解决你的需求：

SELECT user, A
FROM t
ORDER BY COUNT(*) OVER (PARTITION BY user) DESC, user, A;

逻辑解释： #

• COUNT(*) OVER (PARTITION BY user)：这个窗口函数会为每一行计算当前用户对应的总行数（也就是分组基数），但不会像GROUP BY那样合并行。
• ORDER BY先按这个计算出的分组基数降序排列，这样行数多的用户组会排在最前面；接着按user排序确保同一用户的行聚在一起；最后可以按A排序（可选，根据你需要的行内顺序调整）。

执行这个语句后，你会得到和预期完全一致的结果：

Erik 1278
Erik 8
Erik 126
Bob 16287
Bob 162
Alice 9723
Daniel 7

如果需要在结果中显示每个用户的行数，可以把窗口函数的结果包含在SELECT里：

SELECT 
  user, 
  A,
  COUNT(*) OVER (PARTITION BY user) AS total_rows
FROM t
ORDER BY total_rows DESC, user, A;

方案二：子查询关联（兼容旧版数据库，如MySQL 5.x） #

如果你的数据库不支持窗口函数（比如MySQL 5.7及以前版本），可以用子查询先计算每个用户的行数，再和原表关联排序：

SELECT t.user, t.A
FROM t
JOIN (
  SELECT user, COUNT(*) AS total_rows
  FROM t
  GROUP BY user
) AS user_stats ON t.user = user_stats.user
ORDER BY user_stats.total_rows DESC, t.user, t.A;

逻辑解释： #

1. 子查询user_stats先按用户分组，计算每个用户的总行数。
2. 把原表t和这个子查询关联，得到每一行对应的用户总行数。
3. 最后按总行数降序、用户名排序，同样能达到目标效果。

内容的提问来源于stack exchange，提问作者Basj