在遍历查询结果时识别重复项 [英] Recognizing duplicates while iterating through query results

问题描述

这是我之前问过的问题.

我可以很好地计算排名(包括平局的逻辑)；当我遇到重复排名的第一个实例时，问题是检测未来的重复.

I can calculate the rank (including the logic for ties) just fine; the issue is detecting future duplicates when I come across the first instance of a duplicate rank.

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 队平局，但反之亦然.

The "tie" logic above will capture team #4 as having tied with team #3, but not vice versa.

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

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?

@Airzooka 给了我一个潜在的解决方案，但我很想知道是否有更有效的方法来做到这一点(甚至可能在 SQL 级别).

@Airzooka gave me a potential solution, but I'm curious to know if there's a more efficient way to do it (possibly at the SQL level even).

谢谢.

推荐答案

在伪代码中:

loop through rows as row1
    loop through rows as row2
        if row1 ain't row2 and row1.points == row2.points, append T    

更新:

好的，这个怎么样，因为你是按胜利排序你的结果集，无论如何:尝试将每一行的信息存储在一个临时数组或变量中，比如 $previousTeamWins，$previousTeamName 等.然后您可以比较当前和前一个并根据此分配 T.因此，您实际上将分配延迟到下一次迭代(或在最后一行的情况下直到循环退出).一次遍历行集，应该可以完成工作.

Ok, how about this, since you're ordering your result set by wins, anyhow: try storing information about each row in a temporary array or variables, like $previousTeamWins, $previousTeamName, etc. Then you can compare the current and the previous and assign the T based on that. So you're effectively delaying the assignment until the following iteration (or until the exit of the loop in the case of the final row). One trip through the row set, should get the job done.

这篇关于在遍历查询结果时识别重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！