这是一个后续的问题，我以前问 。
我可以计算排名（包括关系逻辑）就好了; 当我遇到一个副本等级的第一个实例的问题是检测未来重复 。
下面是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;
下面是示例SQL结果集我将通过PHP循环：
team_id wins
--------------
10 52
2 48
5 46
11 46
3 42
9 39
...
这里是我的显示，需要追加“T-”所有绑行列PHP代码：
$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
更新：
好了，这个怎么样，因为你订购你的结果由胜设置，总之：尽量存储在临时数组或变量，如对各行信息$previousTeamWins ， $previousTeamName ，等等，那么你可以对比本期和上一并基于该分配吨。 所以你有效的延迟分配，直至以后的迭代（或直到循环的最后一行的情况下，退出）。 通过行集一趟，应该把工作做好。
那这个呢？
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`
爱，还是看你的代码。 因此，在这一天结束时，你只需要输出一个或两个团队，对不对？ 如果一个领导者是团队的问题，和两个其他。 如果是这样的情况下，尝试这样的事情（与警告说，这是未测试）：
$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 。
感谢所有通过这个不自然繁琐的问题，与我的轴承！
