我正在创建一个比赛网站。在网上的某个地方，我找到了一条包含2个桌子，游戏和团队的sql语句，并输出锦标赛积分。
以下是团队tabel：
id tname poule
1 ZF Eger heren1A
2 Pro Recco heren1A
3 Sintez Kazan heren1A
4 Szolnoki VSE heren1A
5 Sintez Kazan 2 heren1B
6 Szolnoki VSE 2 heren1B
下面是游戏tabel：
id date hteam ateam hscore ascore gamefield poule played
1 2008-01-01 20:00:00 1 2 0 0 Veld 1 heren1A 0
2 2008-01-01 20:00:00 3 4 10 8 Veld 2 heren1A 1
下面的代码对数据库执行sql查询，并输出联赛排名。
$pouleA = 'heren1A';
$pouleB = 'heren1B';
$query = $mysqli->prepare('SELECT poule AS Poule, tname AS Team, Sum(WG) AS WG,Sum(W) AS W,Sum(G) AS G,Sum(V) AS V, SUM(DV) as DV,SUM(DT) AS DT,SUM(S) AS S,SUM(P) AS P FROM( SELECT hteam Team, 1 WG, IF(hscore > ascore,1,0) W, IF(hscore = ascore,1,0) G, IF(hscore < ascore,1,0) V, hscore DV, ascore DT, hscore-ascore S, CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END P FROM games WHERE played = 1 AND poule = ? OR poule = ? UNION ALL SELECT ateam, 1, IF(hscore < ascore,1,0), IF(hscore = ascore,1,0), IF(hscore > ascore,1,0), ascore, hscore, ascore-hscore S, CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END FROM games WHERE poule = ? OR poule = ?) as tot JOIN teams t ON tot.Team=t.id GROUP BY Team ORDER BY SUM(P) DESC, s DESC');
$query->bind_param('ssss', $pouleA, $pouleB, $pouleA, $pouleB);
该查询的问题在于，无论是否进行比赛，它总是计算每个比赛。我如何在查询中使用来自games-tabel的play-column，以便仅获取已进行的比赛？
*编辑：
如果我将查询更改为：
SELECT poule AS Poule, tname AS Team, Sum(WG) AS WG,Sum(W) AS W,Sum(G) AS G,Sum(V) AS V, SUM(DV) as DV,SUM(DT) AS DT,SUM(S) AS S,SUM(P) AS P FROM( SELECT hteam Team, IF(played = 1,1,0) WG, IF(hscore > ascore,1,0) W, IF(hscore = ascore,1,0) G, IF(hscore < ascore,1,0) V, hscore DV, ascore DT, hscore-ascore S, CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END P FROM games WHERE played = 1 AND poule = ? OR played = 1 AND poule = ? UNION ALL SELECT ateam, 1, IF(hscore < ascore,1,0), IF(hscore = ascore,1,0), IF(hscore > ascore,1,0), ascore, hscore, ascore-hscore S, CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END FROM games WHERE played = 1 AND poule = ? OR played = 1 AND poule = ?) as tot JOIN teams t ON tot.Team=t.id GROUP BY Team ORDER BY SUM(P) DESC, s DESC
没有参加比赛的球队没有被选中，但是我仍然希望他们被选中，因为即使没有比赛，我也需要展示完整的状态。
最佳答案
尝试这个：
$query = $mysqli->prepare('SELECT poule AS Poule, tname AS Team, Sum(WG) AS WG,Sum(W) AS W,Sum(G) AS G,Sum(V) AS V, SUM(DV) as DV,SUM(DT) AS DT,SUM(S) AS S,SUM(P) AS P FROM( SELECT hteam Team, 1 WG, IF(hscore > ascore,1,0) W, IF(hscore = ascore,1,0) G, IF(hscore < ascore,1,0) V, hscore DV, ascore DT, hscore-ascore S, CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END P FROM games WHERE played = 1 AND poule = ? OR poule = ? UNION ALL SELECT ateam, 1, IF(hscore < ascore,1,0), IF(hscore = ascore,1,0), IF(hscore > ascore,1,0), ascore, hscore, ascore-hscore S, CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END FROM games WHERE played = 1 AND poule = ? OR poule = ?) as tot JOIN teams t ON tot.Team=t.id GROUP BY Team ORDER BY SUM(P) DESC, s DESC');
使用
FROM games WHERE poule = ? or poule = ?接近尾声。我只是说FROM games WHERE played = 1 AND poule = ? OR poule = ?。