我正在尝试计算团队在有序MySQL结果集中的排名，而我遇到的问题是检测 first 团队的关系以显示并列值. /p>
例如，说结果集如下:
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&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".)
问题就变成了:在遍历结果以查找当前分数是否是列表的并列/重复时，我该如何向前看"，因此我可以将其与列并列地视为并列随后的骗子?
谢谢.
如果我首先将结果存储在表中，例如下面的"wins"，则可以实现Ignacio的代码:
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 . . .但是，在我看来，我已经开始使用此结果集，而该实际上并不能解决我的问题.
为避免混淆，我发布了>后续问题" .
解决方案此问题已在此处回答.查询:
SELECT a.team_id, a.wins, count(*) instances FROM (SELECT s1.team_id, sum(s1.score>s2.score) wins FROM scoreboard s1 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) AS a LEFT JOIN (SELECT sum(s1.score>s2.score) wins FROM scoreboard s1 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) AS b ON a.wins = b.wins GROUP BY a.team_id, b.wins ORDER BY a.wins DESC;这给出了输出...
================================= |team_id | wins |instances | ================================= |10 | 44 |1 | |2 | 42 |3 | //tie |9 | 42 |3 | //tie |5 | 42 |3 | //tie |3 | 41 |1 | |11 | 40 |1 | |... | | | =================================然后，在PHP中，通过检查$row['instances'] > 1的时间，我将能够检测到所有联系.
I am trying to calculate a ranking of a team in an ordered MySQL result set, and the issue I'm having is detecting ties for the first team to show up with the tied value.
For example, say the result set is the following:
team_id pts --------------- 1 89 2 87 3 76 4 76 5 52I calculate the ranking of the team with the following 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&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 }The "tie" logic above will capture team #4 as having tied with team #3, but not vice versa.
In other words, for team #3, $rnk = 3, while for team #4, $rnk = T-3. (Both should be "T-3".)
So the question becomes: how do I "look ahead" while iterating through the results to find out if the current score is a tie/duplicate of scores further down the list, so I can treat it as a tie along with the subsequent dupes?
Thanks.
EDIT: I can implement Ignacio's code if I first store results in a table, such as "wins" below:
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;This gives the following results:
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 . . .However, it occurs to me that I was already getting to this result set, and this doesn't actually solve my problem.
To avoid confusion, I've posted the "follow-up" problem separately.
解决方案This question has been answered here.
The query:
SELECT a.team_id, a.wins, count(*) instances FROM (SELECT s1.team_id, sum(s1.score>s2.score) wins FROM scoreboard s1 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) AS a LEFT JOIN (SELECT sum(s1.score>s2.score) wins FROM scoreboard s1 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) AS b ON a.wins = b.wins GROUP BY a.team_id, b.wins ORDER BY a.wins DESC;This gives the output...
================================= |team_id | wins |instances | ================================= |10 | 44 |1 | |2 | 42 |3 | //tie |9 | 42 |3 | //tie |5 | 42 |3 | //tie |3 | 41 |1 | |11 | 40 |1 | |... | | | =================================Then, in PHP, I'll be able to detect all ties by checking when $row['instances'] > 1.
更多推荐
在PHP中遍历MySQL结果时检测将来的重复值
发布评论