MySQL 排行榜

Question

我的数据库中有 2 个表，如下所示:

下面是团队表:

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

下面是游戏表:

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

我遇到的问题是:

没有参加比赛的球队不会被选中，但我仍然希望他们被选中，因为我需要显示完整的球队，即使没有比赛。如何更改我的查询，以便在未进行任何比赛时也显示联赛排名？

Answer 1

您应该使用team作为 anchor ，并使用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