php - 如何在 html 表中填充 UNION ALL 查询数据

Question

关闭。这个问题需要debugging details .它目前不接受答案。












想改善这个问题吗？更新问题，使其成为 on-topic对于堆栈溢出。

去年关闭。




Improve this question




如何使用 PHP 和 MYSQL 获取的两侧标题填充 html 表中的数据。我没有得到输出显示，正如我想要的那样，就像在所需的输出图像中一样。

$query = $db->prepare("SELECT player, score FROM cricket_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
UNION ALL
SELECT player, score FROM football_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'");
$query->execute; 
echo '<table><tr><th>Player Name</th><th>Cricket</th><th>Football</th></tr>';
while($row = $query->fetch(PDO:: FETCH_ASSOC) ) {
echo '<tr>
        <td>'.$row['player'].'</td>
        <td>'.$row['score'].'</td>
     </tr>';
}
echo '</table>';

这是所需的 output



这是当前的输出

Answer 1

您可以使用 UNION ALL 并对每列所需的值求和:

这是您的 SQL。请注意，它将使用 union all 运行两个查询，然后对每个值求和。我们将使用“0”(零)技巧来忽略“错误”表:

SELECT x.player, SUM(x.cricket_score) as cricket_score, SUM(x.football_score) as football_score FROM (
    SELECT player, SUM(score) as cricket_score, 0 as football_score
      FROM cricket_table
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
     UNION ALL
    SELECT player, 0 as cricket_score, SUM(score) as football_score
      FROM football_table 
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
) x
GROUP BY x.player

并应用于代码:

$query = $db->prepare("    SELECT x.player, SUM(x.cricket_score) as cricket_score, SUM(x.football_score) as football_score FROM (
    SELECT player, SUM(score) as cricket_score, 0 as football_score
      FROM cricket_table
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
     UNION ALL
    SELECT player, 0 as cricket_score, SUM(score) as football_score
      FROM football_table 
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
) x
GROUP BY x.player");
$query->execute();
echo '<table><tr><th>Player Name</th><th>Cricket</th><th>Football</th></tr>';
while($row = $query->fetch(PDO:: FETCH_ASSOC) ) {
    echo '<tr>
        <td>'.$row['player'].'</td>
        <td>'.$row['cricket_score'].'</td>
        <td>'.$row['football_score'].'</td>
     </tr>';
}
echo '</table>';

使用此 SQL，您将获得板球或足球已经设置的值。