在MySQL中存储排名位置

Question

我在论坛上搜索了解决我的问题的方法。我的问题是，我找不到如何保存每次举办的比赛的排名位置。我创建了两个表和一个查询，如下所示： 竞争者（cid int自动递增主键，名字varchar（25），姓氏varchar（25））; comps（compid int自动递增主键，tournement int，cid int，points int）;

   select @rowno:= @rowno+1 as position,  rank.*
   from (
   select name,lastname,SUM(points) as pts,group_concat(points) as round FROM
   (select cid,tournament,points from comps 
    group by cid,tournament  order by points)total
    join competitors c on c.cid = total.cid
    cross join   (select @rowno := 0) r
    group by total.cid
    order by pts desc) rank
    order by pts desc

以下是SQLFiddle演示

我想实现的目标是，当用户点击竞争对手的个人资料时，像这样显示每个比赛的排名：

Name: Competitor One
Tournament 1: 1st 100  pts
Tournament 2: 2nd  80  pts
Tournament 3: 10th 30  pts

我已经对点进行了分组，但是我不知道如何在位置方面做到这一点。从这个查询中是否可能实现，还是我必须创建一个新的表，例如“positions”（pid int auto_increment primary key，tournament int，cid int，position int），在其中为每个竞争者插入每个位置。

希望有人能理解我的问题，并能给我一些提示或解决方案。

- Headpetrol

---

这些不是有序的吗？一个位置不就是点的数量吗？ - Marco Mura

点数已经通过group_concat正确排序，但我希望以与@rowno变量相同的方式存储位置。我希望这样做可以让位置和点数在一起。 - Headpetrol

抱歉，如果位置是由点给出的，我不认为添加另一个位置字段有用。但是你可以添加一个名为“position”的字段，然后按照你的意愿进行操作。 - Marco Mura

Answer 1

如果我的理解是正确的，那么我希望这对你有用。

select *,(SELECT COUNT(*) 
             FROM comps AS cmp2  
             WHERE cmp1.points < cmp2.points AND cmp1.tournament = cmp2.tournament) + 1  AS position
from competitors AS c1
inner join comps AS cmp1 on c1.cid = cmp1.cid
order by  c1.name,c1.lastname,cmp1.compid, position ASC

工作中的Sql Fiddle 这里

- jamseernj

---

这是我想要做的。谢谢！ - Headpetrol

我正在尝试将相同的东西应用到联赛积分榜中，但我无法弄清楚。我的联赛积分榜设置如下[链接]（http://sqlfiddle.com/#!2/a6b87/1/0），我想在每个比赛日后获得相同的位置。 - Headpetrol

Answer 2

如果您想按比赛返回结果，可以这样做：

select * from competitors
inner join comps on competitors.cid = comps.cid
order by tournament, points desc

如果您想要进行分组，最简单的方法是使用子查询来对每个竞争者的积分进行求和：

select *,(select sum(points) from comps where competitors.cid=comps.cid) as points
from competitors
order by points desc

不，你不需要一个带有位置的表格，因为那是计算出来的数据。唯一需要这样做的原因是出于性能考虑（并且每隔一段时间自动更新它）。

- Alfons