mysql - 在 mysql select 中显示行数

Question

我有这个声明:

SELECT count(s.name), s.name, avg(a.rate),  @curRank := @curRank + 1 AS rank 
FROM `avatec_objects_comments` as a, avatec_objects as s, (SELECT @curRank := 0) r
WHERE a.oid =s.id and s.status=0 
GROUP BY s.name 
ORDER BY  avg(a.rate) desc, count(s.name) desc

这是我想要的结果(见旁边的数字)

如何根据 count(s.name) 和 avg(a.rate) 设置排名，就像上面期望的结果一样？

Answer 1

MySQL 在group by 中使用变量似乎有问题。解决方案是使用子查询。此外，可以使用显式 join 语法改进您的查询:

SELECT cnt, name, avgrate,
       (@curRank := @curRank + 1) AS rank 
FROM (SELECT count(o.name) as cnt, o.name, avg(oc.rate) as avgrate, 
      FROM avatec_objects o JOIN
           avatec_objects_comments oc
           ON oc.oid = o.id 
      WHERE o.status = 0
      GROUP BY o.name
     ) oc CROSS JOIN
     (SELECT @curRank := 0) vars
ORDER BY  avgrate desc, cnt desc;