我正在尝试计算有序 MySQL结果集中团队的排名,我遇到的问题是检测第一个团队出现绑定值的关系.

例如,假设结果集如下：

team_id    pts
---------------
1          89
2          87
3          76
4          76
5          52

我使用以下PHP计算团队的排名：

$i = 0;
while($row = MysqL_fetch_assoc($r)) { //ITerate thru ordered (desc) sql results
    ++$i;
    ($row['pts'] == $prev_val)
        ? $rnk = 'T-' . $rnk    //same as PRevIoUs score,indicate tie
        : $rnk = $i;            //not same as prevIoUs score
    $rnk = str_replace('T-t-','T-',$rnk); //eliminate duplicative tie indicator
    if ($row['team_id'] == $team_id) { //current team in resultset matches team in question,set team's rank
        $arr_ranks['tp']['cat'] = 'total Points';
        $arr_ranks['tp']['actual'] = number_format($row['pts'],1);
        $arr_ranks['tp']['league_rank'] = $rnk;
        $arr_ranks['tp']['div_rank'] = $div_rnk;
    }
    else if ($i == 1) { //current team is category leader (rank=1) and is not team in question,set current team as leader
        $arr_ranks['tp']['leader'] = "<a href='index.PHP?view=franchise&amp;team_id=" . $row['team_id'] . "'>" . get_team_name($row['team_id']) . '</a> (' . number_format($row['pts'],1) . ')';
    }
    $prev_val = $row['pts']; //set current score as prevIoUs score for next iteration of loop
}

上面的“平局”逻辑将捕获团队#4与团队#3并列,但反之则不然.

换句话说,对于团队#3,$rnk = 3,而对于团队#4,$rnk = T-3. (两者都应该是“T-3”.)

所以问题就变成了：如何在搜索结果的同时“向前看”,以查明当前得分是否是列表中下方得分的平局/重复,那么我可以将其作为一个平局与随后的欺骗一起对待？

谢谢.

编辑：如果我首先将结果存储在一个表中,我可以实现Ignacio的代码,例如下面的“wins”：

select
    s1.team_id,t.division_id,sum(s1.score>s2.score) tot_wins,(   select count(*)
                From wins
                where team_id <> s1.team_id
                and wins > (select wins
                            from wins
                            where team_id = s1.team_id)
            )+1 as rnk
from
    scoreboard s1
    left join teams t
        on s1.team_id = t.team_id
    left join scoreboard s2
        on s1.year=s2.year and s1.week=s2.week and s1.playoffs=s2.playoffs and s1.game_id=s2.game_id and s1.location<>s2.location
group by s1.team_id
order by tot_wins desc;

这给出了以下结果：

team_id    division_id   tot_wins  rnk
--------------------------------------
10         1             44        1
2          1             42        2
3          2             42        2
8          1             39        4
5          2             37        5
. . .

但是,我发现我已经到了这个结果集,这实际上并没有解决我的问题.

为避免混淆,我发布了the “follow-up” problem separately.

$ranks = array();
while ($row = MysqL_fetch_assoc($result)) {
    $ranks[$row['pts']][] = $row['team_id'];
}

$ranks[89] = array(1);
$ranks[87] = array(2);
$ranks[76] = array(3,4);
$ranks[52] = array(5);