MySQL多表JOIN查询COUNT+ORDER BY失效及only_full_group_by报错如何解决

原SQL存在以下几处问题：

• GROUP BY字段错误：你需要按评论者钱包地址分组统计，不是按单条评论内容cr.Review分组，同时你SELECT中返回的cr.id是单条评论的主键，不属于分组字段也没有用聚合函数包裹，自然触发only_full_group_by模式的报错。
• 关联逻辑错误：直接JOIN两张表会产生笛卡尔积，比如一个用户有2条评论、2条粉丝相关记录，关联后会生成4条中间数据，统计出来的评论数、粉丝数都会翻倍，完全不符合预期。
• 排序逻辑错误：要取评论数最多的Top用户，应该按评论数倒序排序，你写的ASC是正序，会先展示评论数最少的用户。
• 字段别名不匹配：最终输出需要TotalReview、TotalFollowers两个别名，你原SQL的别名设置不符合要求。

思路是先分别统计每个用户的评论数、粉丝数，再做关联，避免笛卡尔积问题：

SELECT 
  ROW_NUMBER() OVER(ORDER BY t1.TotalReview DESC) AS id,
  t1.wallet_address,
  t1.TotalReview,
  t2.TotalFollowers
FROM (
  -- 先统计每个用户的有效评论数，排除钱包地址123
  SELECT 
    wallet_address,
    COUNT(id) AS TotalReview
  FROM coin_review
  WHERE wallet_address != '123'
  GROUP BY wallet_address
) t1
LEFT JOIN (
  -- 统计每个用户的粉丝总数，对应示例数据按from_wallet_address分组统计
  SELECT 
    from_wallet_address AS wallet_address,
    COUNT(DISTINCT to_wallet_address) AS TotalFollowers
  FROM un_user_follower
  GROUP BY from_wallet_address
) t2 ON t1.wallet_address = t2.wallet_address
ORDER BY t1.TotalReview DESC;

如果你用的是MySQL 5.7及以下版本不支持窗口函数，可以用变量生成行号：

SELECT 
  @row_num := @row_num + 1 AS id,
  t1.wallet_address,
  t1.TotalReview,
  t2.TotalFollowers
FROM (
  SELECT 
    wallet_address,
    COUNT(id) AS TotalReview
  FROM coin_review
  WHERE wallet_address != '123'
  GROUP BY wallet_address
  ORDER BY TotalReview DESC
) t1
LEFT JOIN (
  SELECT 
    from_wallet_address AS wallet_address,
    COUNT(DISTINCT to_wallet_address) AS TotalFollowers
  FROM un_user_follower
  GROUP BY from_wallet_address
) t2 ON t1.wallet_address = t2.wallet_address,
(SELECT @row_num := 0) AS init;

说明：如果你的粉丝逻辑是「其他用户关注当前评论者」，即评论者是被关注方，只需要调整粉丝统计子查询的分组字段为to_wallet_address，统计COUNT(DISTINCT from_wallet_address)即可适配。

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