php - 比较来自 2 个 While 循环的值 (PHP MYSQL)

Question

我正在尝试为梦幻足球联赛中的正面对决创建所有时间排名。我正在使用两个单独的查询并使用两个单独的 while 循环获取数据。我希望我可以将两者与 IF 语句进行比较，但不行。

我的查询如下:

$query5 = "SELECT *, SUM(POINTS) ";
    $query5 .= " FROM SCHEDULE";
    $query5 .= " WHERE owner = 'DUSTIN'";
    $query5 .= " AND OPPONENT = 'LEE'";
    $query5 .= " GROUP BY WEEK ";
    $result5 = mysqli_query($con, $query5);

    $query6 = "SELECT *, SUM(POINTS) ";
    $query6 .= " FROM SCHEDULE";
    $query6 .= " WHERE owner = 'LEE'";
    $query6 .= " AND OPPONENT = 'DUSTIN'";
    $query6 .= " GROUP BY WEEK ";
    $result6 = mysqli_query($con, $query6);

我的PHP:

while($row5 = mysqli_fetch_assoc($result5)){$row5 ["SUM(POINTS)"];
while($row6 = mysqli_fetch_assoc($result6)){$row6 ["SUM(POINTS)"];  
if ( $row5 ["SUM(POINTS)"] > $row6 ["SUM(POINTS)"] ){
$win++;
}
else {
$loss++;
}
}
}

该函数适用于 $row6，但对于 $row5，它会一遍又一遍地获取相同的数字，而不是在数组中循环。

这是示例数据:

YEAR    WEEK    OWNER   OPPONENT    POSITION    PLAYER  POINTS
2008    1   Dustin  Brandon RB  Chris Johnson, Ten RB   19.7
2008    1   Dustin  Brandon QB  Ben Roethlisberger, Pit QB  13.1
2008    1   DUSTIN  BRANDON D/ST    Titans D/ST, Ten D/ST   17
2008    1   DUSTIN  BRANDON WR  Roddy White, Atl WR 5.4
2008    1   DUSTIN  BRANDON RB  Laurence Maroney, NE RB 6.1
2008    1   BRANDON DUSTIN  RB  Adrian Peterson, Min RB 21.4
2008    1   BRANDON DUSTIN  RB  Edgerrin James, Ari RB  15
2008    1   BRANDON DUSTIN  WR  Larry Fitzgerald, Ari WR    9.1
2008    1   BRANDON DUSTIN  k   Joe Nedney, SF K    4.5
2008    1   BRANDON DUSTIN  WR  Chris Chambers, SD WR   11.4

我需要将 Dustin 在第 1 周的总分相加，并将它们与 Brandon 在第 1 周的总分进行比较。并指出获胜的球队获胜。

Answer 1

下面的查询将返回 dustin、lee 的总获胜次数和平局总数。胜利的定义是在同一周内比对手得分更多。

查询假定两个玩家每周至少有一些积分。如果不是这种情况，您将不得不从内部联接转换为完全联接。

SELECT
    COUNT(CASE WHEN t1.points > t2.points THEN 1 END) dustin_win_count,
    COUNT(CASE WHEN t2.points > t1.points THEN 1 END) lee_win_count,
    COUNT(CASE WHEN t2.points = t1.points THEN 1 END) tie_count
FROM
    (SELECT WEEK, SUM(POINTS) points
    FROM SCHEDULE WHERE
    OWNER = 'DUSTIN'
    AND OPPONENT = 'LEE'
    GROUP BY WEEK) t1
    JOIN (SELECT WEEK, SUM(POINTS) points
    FROM SCHEDULE WHERE
    OWNER = 'LEE'
    AND OPPONENT = 'DUSTIN'
    GROUP BY WEEK) t2 ON t1.WEEK = t2.WEEK