我的数据库中有2个表，如下所示：
以下是团队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查询，并输出联赛排名。
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
我有的问题是：
没有参加比赛的球队没有被选中，但是我仍然希望他们被选中，因为即使没有比赛，我也需要展示完整的状态。如何更改我的查询，以便在没有比赛进行时也显示联赛排名？
最佳答案
您应该将team用作锚，并使用LEFT JOIN进行统计：
SELECT
t.poule,
t.tname AS Team,
IFNULL(Sum(WG), 0) AS WG,
IFNULL(Sum(W) , 0) AS W,
IFNULL(Sum(G) , 0) AS G,
IFNULL(Sum(V) , 0) AS V,
IFNULL(SUM(DV), 0) as DV,
IFNULL(SUM(DT), 0) AS DT,
IFNULL(SUM(S) , 0) AS S,
IFNULL(SUM(P) , 0) AS P
FROM teams t
LEFT JOIN (
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 ON tot.Team=t.id
GROUP BY t.poule,t.tname
ORDER BY SUM(P) DESC, s DESC
这样，所有团队都将被退回，包括那些没有统计数据的团队。
结果*
heren1A Sintez Kazan 1 1 0 0 10 8 2 3
heren1A Szolnoki VSE 1 0 0 1 8 10 -2 0
heren1A ZF Eger 0 0 0 0 0 0 0 0
heren1A Pro Recco 0 0 0 0 0 0 0 0
heren1B Sintez Kazan 2 0 0 0 0 0 0 0 0
heren1B Szolnoki VSE 2 0 0 0 0 0 0 0 0
关于mysql - MySQL排名表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31983330/