mysql - 排名未正确确定

Question

我正在使用这个查询:

SELECT A.place_idx,A.place_id,B.TOTAL_CNT,(@r := @r + 1) AS rank FROM CUSTOM_LIST
AS A
INNER JOIN
(SELECT  @r := 0)
AS C
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS B ON B.place_id=A.place_id order by B.TOTAL_CNT desc;

结果如下:

但我想要这样的结果:

我需要如何修改我的查询？我做错了什么？

Answer 1

SELECT *,(@r := @r + 1) AS rank FROM 
(
  SELECT A.place_idx,A.place_id,B.TOTAL_CNT FROM CUSTOM_LIST
  AS A
  INNER JOIN
  (SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
  AS B ON B.place_id=A.place_id order by B.TOTAL_CNT desc
) AS T, (SELECT  @r := 0) AS tt