在PHP中遍历MySQL结果时检测将来的重复值 [英] Detect future duplicate values while iterating through MySQL results in PHP
问题描述
我正在尝试计算团队在有序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 52
I 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结果时检测将来的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
