时间戳

首页 > 解决方案 > Mysql 依靠 ORDERBY 不能与 Join 一起使用

php - Mysql 依靠 ORDERBY 不能与 Join 一起使用

问题描述

我有两个表“coin_review”和“un_user_follower”，我想获取“Top Reviewer”（除了我（123），并且他的大多数评论存在，他的追随者）这是我的表“coin_review”

id          Review              wallet_address      
1           Lorem Ipsum1        123 
2           Lorem Ipsum2        1234
3           Lorem Ipsum3        1234
4           Lorem Ipsum4        12345

...

这是我的表“un_user_follower”

id          from_wallet_address         to_wallet_address       
1           1234                            11111       
2           1234                            12254
3           123                             25141
4           12345                           14144
5           12345                           14798
...

现在作为结果，我想获得顶级评论者（存在最多记录）和他的追随者总数例如我想要以下输出作为结果

id          wallet_address          TotalReview         TotalFollowers
1           1234                        2                   2
2           12345                       1                   1

为此，我尝试了以下查询

SELECT cr.id,COUNT(cr.Review) as Total,COUNT(usf.to_wallet_address)
FROM coin_review cr
JOIN un_user_follower usf
ON usf.to_wallet_address=cr.wallet_address
WHERE cr.wallet_address!='123'
GROUP BY cr.Review
ORDER BY Total ASC

但是我遇到了错误，我该如何解决这个问题？我错在哪里？

SELECT list is not in GROUP BY clause and contains nonaggregated column 'Uni_back.cr.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

标签： phpmysqlsql

解决方案

排名前两位的评论者及其追随者人数。首先对表进行分组，然后进行 LEFT JOIN。

SELECT cr.wallet_address, cr.TotalReview, coalesce(fl.TotalFollowers,0) TotalFollowers
FROM (
  SELECT wallet_address, COUNT(Review) as TotalReview
  FROM coin_review 
  WHERE wallet_address!='123'
  GROUP BY wallet_address
  ORDER BY COUNT(Review) DESC
  LIMIT 2 
)  cr
LEFT JOIN (
  SELECT from_wallet_address,  COUNT(to_wallet_address) TotalFollowers
  FROM  un_user_follower
  WHERE from_wallet_address!='123'
  GROUP BY from_wallet_address
) fl ON cr.wallet_address = fl.from_wallet_address
ORDER BY cr.TotalReview ASC

db<>小提琴

返回

wallet_address  TotalReview TotalFollowers
12345   1   2
1234    2   2

推荐阅读