我有医院的名单,下面有已经计算出来的平均评分。现在我想根据以下查询中医院的平均评分来计算医院列表的排名
SELECT name,
hospitalID,
currentAvgRating,
@curRank := @curRank + 1 AS rank
FROM hospitals h, (SELECT @curRank := 0) r
ORDER BY currentAvgRating DESC
现在,当我想从表中查看所有医院时,上面的查询可以工作,但当我应用下面这样的WHERE子句时,结果是错误的,因为这样会占据行位置。
SELECT name,
hospitalID,
currentAvgRating,
@curRank := @curRank + 1 AS rank
FROM hospitals h, (SELECT @curRank := 0) r where hospitalID = '453085'
ORDER BY currentAvgRating DESC
当我们使用where子句时,有什么方法可以得到正确的结果吗?
最佳答案
如果你继续你刚刚发现的逻辑(“当只有一个列表项时,它不能被排序”)——你会得出结论,你需要选择所有行。但这没什么问题,你可以把它们打包成一个子选择(这甚至不是一个昂贵的选择),然后应用WHERE:
SELECT * FROM (
SELECT name,
hospitalID,
currentAvgRating,
@curRank := @curRank + 1 AS rank
FROM hospitals h, (SELECT @curRank := 0) r
ORDER BY currentAvgRating DESC
) toplist
WHERE toplist.hospitalID = 453085