这是我之前问的一个问题的后续。
我可以很好地计算排名(包括领带的逻辑);问题是当我遇到重复排名的第一个实例时,会检测未来的重复项。
下面是用于获取结果集的 SQL 查询:
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
ORDER BY wins DESC;
以下是我将在 PHP 中循环遍历的示例 SQL 结果集:
team_id wins
--------------
10 52
2 48
5 46
11 46
3 42
9 39
...
这是我用于显示的PHP代码,它需要将"T-"附加到所有并列的等级:
$i = 0;
while($row = mysql_fetch_assoc($r)) { //iterate thru ordered (desc) SQL results
++$i;
($row['wins'] == $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 Wins';
$arr_ranks['tp']['actual'] = number_format($row['wins'],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['wins']) . ')';
}
$prev_val = $row['wins']; //set current score as previous score for next iteration of loop
}
上面的"平局"逻辑将捕获团队 #4 与团队 #3 并列,反之则不然。
换句话说,对于团队#3,$rnk = 3
,而对于团队#4,$rnk = T-3
。(两者都应为"T-3"。
所以问题变成了:在迭代结果时,我如何"向前看",以确定当前分数是否是列表后面分数的平局/重复,以便我可以将其视为平局以及随后的重复?
@Airzooka给了我一个潜在的解决方案,但我很好奇是否有更有效的方法可以做到这一点(甚至可能在 SQL 级别)。
谢谢。
在伪代码中:
loop through rows as row1
loop through rows as row2
if row1 ain't row2 and row1.points == row2.points, append T
更新:
好的,怎么样,因为你是按wins对结果集进行排序的,无论如何:尝试将有关每一行的信息存储在临时数组或变量中,如$previousTeamWins
, $previousTeamName
等。然后,您可以比较当前和以前的值,并在此基础上分配T。因此,您实际上将赋值延迟到下一次迭代(或者在最后一行的情况下直到循环退出)。一次穿越行集,应该可以完成工作。
这个呢?
SELECT
t1.*,
EXISTS (
SELECT *
FROM `teams` as t2
WHERE t2.pts = t1.pts AND t1.id != t2.id
) as `tied`
FROM `teams` as t1
...
试试这个,希望它对你有用
SELECT t1.`id`,t1.`pts`,t1.`team_id`,
IF(t1.`pts` = t2.`pts` AND t1.`id` != t2.`id` ,1,0) AS `tied`
FROM `teams` t1
LEFT JOIN `teams` t2 on t2.`pts` = t1.`pts` AND t2.`id` != t1.`id`
GROUP bY t1.`id`
Oi,还在看你的代码。所以归根结底,你只想输出一两个团队,对吧?一个是领导者是有问题的团队,另一个是其他的。如果是这种情况,请尝试以下操作(警告未经过测试):
$i = 0;
while($row = mysql_fetch_assoc($r)) { //iterate thru ordered (desc) SQL results
++$i;
($row['wins'] == $prev_wins)
? $rnk = $prev_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 ($prev_team_id == $team_id) {
$arr_ranks['tp']['cat'] = 'Total Wins';
$arr_ranks['tp']['actual'] = number_format($prev_wins,1);
$arr_ranks['tp']['league_rank'] = $prev_rnk;
$arr_ranks['tp']['div_rank'] = $div_rnk;
if ($row['wins'] == $prev_wins)
{
$arr_ranks['tp']['tie'] = true;
}
else
{
$arr_ranks['tp']['tie'] = false;
}
break;
}
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['wins']) . ')';
}
$prev_wins = $row['wins'];
$prev_team_id = $row['team_id'];
$prev_rnk = $rnk;
}
if ($prev_team_id == $team_id) {
$arr_ranks['tp']['cat'] = 'Total Wins';
$arr_ranks['tp']['actual'] = number_format($prev_wins,1);
$arr_ranks['tp']['league_rank'] = $prev_rnk;
$arr_ranks['tp']['div_rank'] = $div_rnk;
if ($row['wins'] == $prev_wins)
{
$arr_ranks['tp']['tie'] = true;
}
else
{
$arr_ranks['tp']['tie'] = false;
}
}
我想
我通过SQL找到了解决方案!可能不优雅(我以后可以清理它),但这里是...
查询:
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
来检测所有联系。
感谢大家与我一起度过这个不自然的繁琐问题!