php - 如何使用 MySQL 和两个 'where' 子句计算最长连胜

Question

这是我最初关于如何 calculate the longest undefeated streak 的后续问题.

我修改了我的数据表以添加“ field ”列以显示比赛是“主场”还是“客场”:

date         result   venue-----------------------------1980-08-16      W     H1980-08-19      L     A1980-08-23      W     A1980-08-26      W     H1980-08-30      D     Hand so on...

I have been successful in calculating the longest overall streaks thanks to my previous question, but now I'd like to calculate those streaks by venue and I'm not sure how to go about it.

For the most home consecutive home wins, for example, I have tried the following query - but the resulting 'runs' are being returned shorter than they actually are (based on a manual count of the data) and there's no obvious interruption:

SQL:

SELECT
    result,
    venue,
    MIN(date) as StartDate,
    MAX(date) as EndDate,
    COUNT(*) as Games 
FROM
    (
        SELECT
            result,
            venue,
            date, 
            (
                SELECT
                    COUNT(*)
                FROM
                    resultengine R 
                WHERE
                    R.result <> RE.result
                    AND
                    (R.venue = 'H') <> (RE.venue = 'H')
                    AND
                    R.date <= RE.date
            ) as RunGroup
        FROM
            resultengine RE
    ) A 
WHERE
    venue = 'H'
    AND
    result='W' 
GROUP BY
    result, RunGroup 
ORDER BY
    Games

PHP:

$result = mysql_query( /* the SQL statement from above */ );


while($row=mysql_fetch_assoc($result))  
{
    $startrundate = date("d F Y",strtotime($row['StartDate'])); 
    $endrundate = date("d F Y",strtotime($row['EndDate'])); 
    echo "<tr>";   
    echo "<td>".$row['Games']."</td>";
    echo "<td class='tableprofile' style='text-align:right;'>".$startrundate." - ".$endrundate."</td>";  
    echo "</tr>";    
    $rowCount += 1;
}

事实证明这比我想象的要困难，但同样不要相信我离解决方案太远了。它只是弥合了我拥有的和我目前没有做的之间的差距。

更新

我似乎已经解决了这个特定问题。就像更改子句一样简单 (R.venue = 'H') <> (RE.venue = 'H')至 R.venue = RE.venue

Answer 1

以下忽略任何 SQL 并假设您只有一个“游戏”列表并且您想要算出最长的连胜纪录。在这种情况下，我假设“游戏”是一个具有方法 wasWon() 的类。

$currentStreak = 0;
$longestString = 0;

foreach($games as $game) {
    if(!$game->wasWon()) {
        $currentStreak = 0;
        continue;
    }
    $currentStreak++;
    if($currentStreak > $longestStreak) {
        $longestStreak = $currentStreak;
    }
}

如果您想将逻辑扩展为只关心主场比赛，您可以包装整个逻辑:

foreach($games as $game) {
    if($game->wasHomeGame()) {
        if(!$game->wasWon()) {
            $currentStreak = 0;
            continue;
        }
        $currentStreak++;
        if($currentStreak > $longestStreak) {
            $longestStreak = $currentStreak;
        }
    }
}

这最好放在一个更灵活的类中，例如，您可以使用值类来说明播放的位置:

public function getLongestStreak(array $games, Venue $venue) {
    $currentStreak = 0;
    $longestString = 0;
    foreach($games as $game) {
        if($game instanceof Game && $game->wasPlayedAt($venue)) {
            if(!$game->wasWon()) {
                $currentStreak = 0;
                continue;
            }
            $currentStreak++;
            if($currentStreak > $longestStreak) {
                $longestStreak = $currentStreak;
            }
        }
    }
    return $longestStreak;
}

我可以继续描述值类在这里如何工作，但我只是分散自己的注意力，希望我已经解决了你的问题。 ;)