我有医院的名单,下面有已经计算出来的平均评分。现在我想根据以下查询中医院的平均评分来计算医院列表的排名

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

10-06 15:35