SQL Rank 查询优化
我有一个表 Player 的表结构如下
Table Player {
Long playerID;
Long points;
Long rank;
}
，假设玩家 ID 和点数具有有效值，我可以根据单个查询中的点数更新所有玩家的排名吗？如果两个人的积分相同，则他们应该并列排名。
我正在使用hibernate，所以无法执行任何带有变量的查询，所以我想出了这个效率非常低的查询。可以对其进行优化以适应上面指定的约束吗？
update player g1
set g1.rank = 1 +
((SELECT count(*) from
(select * from player) g2
where g2.points > g1.points))
I have the following table structure for a table Player
Table Player {
Long playerID;
Long points;
Long rank;
}
Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank.
I'm using hibernate, so cannot execute any queries with variables, so I came up with this query which is very inefficient. Can this be optimized to work with the constraints specified above?
update player g1
set g1.rank = 1 +
((SELECT count(*) from
(select * from player) g2
where g2.points > g1.points))
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用 MS SQL Server 2005+ T-SQL，我建议您查看一下出色的 ROW_NUMBER()、RANK() AND DENSE_RANK() 和 NTILE() 函数！
在此处了解有关它们的更多信息
If you are using MS SQL Server 2005+ T-SQL, I can recommend having a look at the fantastic ROW_NUMBER(), RANK() AND DENSE_RANK(), and NTILE() functions!
Read more here about them
SQL Server中有四种排名函数。 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE 用于返回分区上每行的排名值。
要了解它们之间的差异，请查看下面给出的 URL
http ://www.freshcodehub.com/Article/50/implement-ranking-functions-in-sql-server
There are four ranking function in SQL Server. ROW_NUMBER, RANK, DENSE_RANK, and NTILE are use to return a ranking value for each row over the partition.
For difference between them with example check the URL given below
http://www.freshcodehub.com/Article/50/implement-ranking-functions-in-sql-server