mysql - 如何将两个嵌套的 MySQL 查询合并到一个 View 中？

Question

我有两个查询，几乎相似，但从未减少，它们必须被视为独立的，因为它们具有不同的含义和值，我想将它们组合成一个 View ，我绑定(bind)了 UNION , 但结果是它们都合并到一个表中，这不是我想要的，我希望它们显示为在一个 View 下完全独立的表，这是我所做的:

CREATE VIEW TEAM_SUMMARY AS
 SELECT DISTINCT COUNTRY.country_name AS CountryName_T1,count(Team1)AS NoOfGames,
    SUM(Team1_score) AS TotalGoalsFor,SUM(Team2_score) AS TotalGoalsAgainst
    FROM COUNTRY,MATCH_RESULTS WHERE
    country_name = Team1
    group by country_name


    UNION 

 SELECT DISTINCT COUNTRY.country_name AS CountryNameT_2,count(Team2)AS NoOfGames,
    SUM(Team2_score) AS TotalGoalsFor,SUM(Team1_score) AS TotalGoalsAgainst
    FROM COUNTRY,MATCH_RESULTS WHERE
    country_name = Team2
    group by country_name;

更新:所以，我当前查询的输出是这样的:

mysql> SELECT * FROM TEAM_SUMMARY;
+----------------------+-----------+---------------+-------------------+
| CountryName          | NoOfGames | TotalGoalsFor | TotalGoalsAgainst |
+----------------------+-----------+---------------+-------------------+
| Algeria              |         1 |             1 |                 1 |
| Argentina            |         4 |             5 |                 1 |
| Australia            |         2 |             2 |                 6 |
| Belgium              |         3 |             5 |                 2 |
| Bosnia & Herzegovina |         1 |             3 |                 1 |
| Brazil               |         6 |             7 |                13 |
| Cameroon             |         2 |             1 |                 8 |
| Chile                |         1 |             3 |                 1 |
| Columbia             |         3 |             7 |                 1 |
| Costa Rica           |         2 |             1 |                 1 |
| Croatia              |         1 |             1 |                 3 |
| Ecuador              |         1 |             0 |                 0 |
| England              |         1 |             1 |                 2 |
| France               |         3 |             5 |                 1 |
| Germany              |         4 |             9 |                 3 |
| Ghana                |         1 |             1 |                 2 |
| Greece               |         1 |             2 |                 1 |
| Honduras             |         2 |             1 |                 5 |
| Iran                 |         1 |             0 |                 0 |
| Italy                |         2 |             0 |                 2 |
| Ivory Coast          |         1 |             2 |                 1 |
| Japan                |         2 |             1 |                 4 |
| Mexico               |         1 |             1 |                 0 |
| Netherlands          |         4 |             4 |                 1 |
| Nigeria              |         2 |             3 |                 3 |
| Portugal             |         1 |             2 |                 1 |
| Russia               |         1 |             1 |                 1 |
| South Korea          |         2 |             2 |                 5 |
| Spain                |         2 |             1 |                 7 |
| Switzerland          |         2 |             4 |                 6 |
| Uruguay              |         2 |             3 |                 4 |
| USA                  |         2 |             2 |                 3 |
| Algeria              |         3 |             6 |                 6 |
| Argentina            |         3 |             3 |                 3 |
| Australia            |         1 |             1 |                 3 |
| Belgium              |         2 |             1 |                 1 |
| Bosnia & Herzegovina |         2 |             1 |                 3 |
| Brazil               |         1 |             4 |                 1 |
| Cameroon             |         1 |             0 |                 1 |
| Chile                |         3 |             3 |                 3 |
| Columbia             |         2 |             5 |                 3 |
| Costa Rica           |         3 |             4 |                 1 |
| Croatia              |         2 |             5 |                 3 |
| Ecuador              |         2 |             3 |                 3 |
| England              |         2 |             1 |                 2 |
| France               |         2 |             5 |                 2 |
| Germany              |         3 |             9 |                 1 |
| Ghana                |         2 |             3 |                 4 |
| Greece               |         3 |             1 |                 4 |
| Honduras             |         1 |             0 |                 3 |
| Iran                 |         2 |             1 |                 4 |
| Italy                |         1 |             2 |                 1 |
| Ivory Coast          |         2 |             2 |                 4 |
| Japan                |         1 |             1 |                 2 |
| Mexico               |         3 |             4 |                 3 |
| Netherlands          |         3 |            11 |                 3 |
| Nigeria              |         2 |             0 |                 2 |
| Portugal             |         2 |             2 |                 6 |
| Russia               |         2 |             1 |                 2 |
| South Korea          |         1 |             1 |                 1 |
| Spain                |         1 |             3 |                 0 |
| Switzerland          |         2 |             3 |                 1 |
| Uruguay              |         2 |             1 |                 2 |
| USA                  |         2 |             3 |                 3 |
+----------------------+-----------+---------------+-------------------+
64 rows in set (0.01 sec)

UPDATE2:每个查询提供32行，这里合并成64行所以不知道哪个属于哪个查询，可以看到USA是最后一行的每个查询，然后它再次以 Algeria 开始用于第二个查询，具有不代表列描述的不同值。

我想要的是这样的:

+------+--------+    +------+--------+
| code | SUM(*) |    | code | SUM(*) |
+------+--------+    +------+--------+
| AAA  |      4 |    | AAA  |      4 |
| BBB  |      3 |    | CCC  |      1 |
+------+--------+    +------+--------+

然后我进行了一些搜索以使用 JOIN，如此处所示 Combine results of two unrelated queries into single view但是，这个场景比我的场景简单得多，无法将其应用到我的场景中，有什么想法吗？

Answer 1

一个 View 不会产生两个 个结果集。但是您可以确定它们的来源:

CREATE VIEW TEAM_SUMMARY AS
   SELECT 'Team1' as which,
          c.country_name AS CountryName_T1, count(Team1) AS NoOfGames,
          SUM(Team1_score) AS TotalGoalsFor,
          SUM(Team2_score) AS TotalGoalsAgainst
    FROM COUNTRY c JOIN
         MATCH_RESULTS mr
         ON c.country_name = mr.Team1
    GROUP BY country_name
    UNION ALL
    SELECT 'Team2' as which,
           c.country_name AS CountryNameT_2,
           count(Team2) AS NoOfGames,
           SUM(Team2_score) AS TotalGoalsFor,
           SUM(Team1_score) AS TotalGoalsAgainst
    FROM COUNTRY c JOIN
         MATCH_RESULTS mr
         ON c.country_name = mr.Team2
    GROUP BY country_name;

注意事项:

• SELECT DISTINCT 和 GROUP BY 几乎总是不必要的(如本例所示。
• 默认使用UNION ALL。仅当您特别希望承担删除重复项的开销时才使用 UNION。
• 表别名使查询更易于编写和阅读。
• 上面添加了一列which来指定每一行来自哪里。