php - 使用特定ID对mysql进行排名

Question

我有这个问题

SELECT concours_photo_like.id , COUNT(concours_photo_like.id) AS nb_like , (@rank := @rank + 1) AS rank 
FROM ( SELECT @rank := 0 ) as r , concours_photo_like
JOIN concours_photo ON concours_photo.id = concours_photo_like.id_concours_photo
WHERE concours_photo.id_concours = 67
GROUP BY concours_photo_like.id_concours_photo 

这里是结果

| id     | nb_like    | rank |
|--------|------------|------|
|    110 |          2 |    2 |
|    104 |          3 |    1 |
|    134 |          1 |    4 |
|    176 |          1 |    5 |
|    113 |          2 |    3 |

如何获得特定 ID 的排名，例如，如果我想要 ID 134 的排名

我试过类似的东西

SELECT concours_photo_like.id , COUNT(concours_photo_like.id) AS nb_like , (@rank := @rank + 1) AS rank 
FROM ( SELECT @rank := 0 ) as r , concours_photo_like
JOIN concours_photo ON concours_photo.id = concours_photo_like.id_concours_photo
WHERE concours_photo.id_concours = 67
AND concours_photo_like.id = 134
GROUP BY concours_photo_like.id_concours_photo 

但是结果是

| id     | nb_like    | rank |
|--------|------------|------|
|    134 |          1 |    1 |

结果应该是 4 而不是 1

Answer 1

MySQL 在将任何值分配给选择列表中的用户变量之前执行 where 子句。

您要么必须在 having 子句中进行过滤，要么将查询包装到子查询中并在外部查询中进行过滤。

拥有:

SELECT concours_photo_like.id , COUNT(concours_photo_like.id) AS nb_like , (@rank := @rank + 1) AS rank 
FROM ( SELECT @rank := 0 ) as r , concours_photo_like
JOIN concours_photo ON concours_photo.id = concours_photo_like.id_concours_photo
WHERE concours_photo.id_concours = 67
GROUP BY concours_photo_like.id_concours_photo
HAVING concours_photo_like.id = 134 

子查询:

SELECT *
FROM
    (SELECT concours_photo_like.id , COUNT(concours_photo_like.id) AS nb_like , (@rank := @rank + 1) AS rank 
     FROM ( SELECT @rank := 0 ) as r , concours_photo_like
     JOIN concours_photo ON concours_photo.id = concours_photo_like.id_concours_photo
     WHERE concours_photo.id_concours = 67
     GROUP BY concours_photo_like.id_concours_photo
    ) t1
WHERE t1.id = 134