php - 如何根据mysql中的计数获得排名

Question

我在根据计数获取排名时遇到问题。

我有两张 table :

1)images
2)images_like

现在，如果 images_like 没有条目，那么它在排名中显示“NULL”。请检查查询和输出:

SELECT im.id, 
   rank 
FROM   (SELECT image_id, 
               @rownum := @rownum + 1 AS rank, 
               Count(*)               AS cnt 
        FROM   images_like, 
               (SELECT @rownum := 0) r 
        GROUP  BY image_id 
        ORDER  BY `cnt` DESC) AS d 
       RIGHT JOIN images AS im 
               ON d.image_id = im.id 

我需要显示 3 not null ....

提前致谢!!!!

Answer 1

尝试这样:

SELECT im.id, d.rank  
FROM
(               
    SELECT t.image_id, @rownum := @rownum + 1 AS rank 
    FROM
        (               
          SELECT image_id, 
                 Count(*) AS cnt 
          FROM   images_like
          GROUP  BY image_id 
          ORDER  BY `cnt` DESC) AS d       
        ) t, 
     (SELECT @rownum := 0) r          
) d     
RIGHT JOIN images AS im ON d.image_id = im.id