自加入加上另一个加入

Question

我有一张国家队的表格

id  country  group
1   Poland   1
2   Greece   1
3   England  2
4   France   2
5   Germany  3
6   Spain    3

我还有每个国家的分数表

fromId  score
1       100
3       50
2       90
4       60

我想做的是在只提供了一个国家名称的情况下，取回一个组中每个国家的分数表。例如，如果我提供"法国"，那么我希望下表返回

country   score
England   50
France    60

我已经成功地加入了的国家队

SELECT
`t1`.`fldCountry`,
`t1`.`fldID`
FROM tblteam t1, tblteam t2
WHERE
t2.fldTeam = t1.fldTeam
AND
t2.fldCountry = 'France'

但现在我被困在如何将其连接回来以获取数据！

有人能帮忙吗？

Answer 1

开始

国家/地区表：

CREATE TEMPORARY TABLE Country
    (
      id INT,
      country VARCHAR(20),
      grp INT
    )
INSERT  INTO  Country
        (
          id,
          country,
          grp
        )
        SELECT  1,
                'Poland',
                1
        UNION ALL
        SELECT  2,
                'Greece',
                1
        UNION ALL
        SELECT  3,
                'England',
                2
        UNION ALL
        SELECT  4,
                'France',
                2
        UNION ALL
        SELECT  5,
                'Germany',
                3
        UNION ALL
        SELECT  6,
                'Spain',
                3 

得分表：

CREATE TEMPORARY TABLE Score ( fromid INT, score INT )
INSERT  INTO Score
        (
          fromid,
          score
        )
        SELECT  1,
                100
        UNION ALL
        SELECT  3,
                50
        UNION ALL
        SELECT  2,
                90
        UNION ALL
        SELECT  4,
                60 

查询：

SELECT  b.country,
        IFNULL(s.score, 0) score
FROM    Country a
        INNER JOIN Country b
            ON a.grp = b.grp
        LEFT JOIN score s
            ON s.fromid = b.id
WHERE   a.country = 'France'

结果：

Country      Score
England      50
France       60

Answer 2

SELECT
    c.country,
    s.score
FROM
    countries c
    INNER JOIN scores s ON c.id = s.fromId
WHERE
    c.group = (SELECT group FROM countries WHERE country = 'France' LIMIT 1);

Answer 3

这样的东西应该可以工作。

Select C2.Country,SUM(S.Score)
FROM Country AS C1 
INNER JOIN Country AS C2
ON C1.Group=C2.Group
INNER JOIN Scores AS S
ON C2.id = S.FromID 
WHERE C1.Country=@Country
GROUP BY C2.Country

Where@Country是您想要查找的国家，就您而言，是法国。