mysql - MYSQL中的此算术运算

我有3个表格，其结构几乎与下面的数据相同，

table name = myshop structure (id_no, time, teams, home, draw, away)

table name = myshop2 structure (id_no, time, teams, home, draw, away)

table name = myshop3 structure (id_no, teams, home, draw, away)

 `TABLE NAME: MYSHOP
  ID_NO TIME    TEAMS                          HOME DRAW    AWAY
  209   00:00   FK PARTIZAN vs CSKA MOSCOW  3.3 3.75    1.85
  210   00:00   RAPID VIENNA vs CELTIC          2.6 3.3 2.3
  211   00:00   SPARTAK MOSCOW vs FK VOZDOVAC   1.3 5.2 6.75
  212   00:00   LUDOGORETS vs DINAMO MOSCOW 2.2 3.35    2.6
  213   00:00   FC THUN vs FC ZURICH            1.9 3.8 2.6
  214   01:00   FC NURNBERG vs BOHEMIANS 1905   1.45    3.9 4.6
  215   01:00   LILLESTROM vs STROMSGODSET  1.0 3.5 3.35
  216   01:00   HALMSTAD vs IF ELFSBORG         2.4 3.5 2.0
  217   05:00   FLANDRIA vs CHACARITA JUNIORS   3.78    2.67    2.54
  218   05:00   CA RIVER PLATE vs DANUBIO   3.25    3.15    2.7
  219   05:00   JUVENTUD vs CERRO           3.55    3.15    2.3

  TABLE NAME: MYSHOP 2
  ID_NO TIME    TEAMS                          HOME DRAW    AWAY
  9     00:00   FK PARTIZAN vs CSKA MOSCOW      2.3 3.75    1.85
  10    00:00   RAPID VIENNA vs CELTIC          1.6 3.3      2.3
  11    00:00   SPARTAK MOSCOW vs FK VOZDOVAC   2.3 5.2      6.75
  12    00:00   LUDOGORETS vs DINAMO MOSCOW     1.2 3.35     2.6
  13    00:00   FC THUN vs FC ZURICH            2.9 3.8      2.6
  14    01:00   FC NURNBERG vs BOHEMIANS 1905   2.453.9      4.6
  15    01:00   LILLESTROM vs STROMSGODSET      1.0 3.5      3.35
  16    01:00   HALMSTAD vs IF ELFSBORG         2.4 3.5      2.0
  17    05:00   FLANDRIA vs CHACARITA JUNIORS   1.782.67     2.54
  18    05:00   CA RIVER PLATE vs DANUBIO   1.25    3.15    2.7
  19    05:00   JUVENTUD vs CERRO           1.55    3.15    2.3


  TABLE NAME: MYSHOP 3
  ID_NO TEAMS                          HOME DRAW    AWAY
  33    FK PARTIZAN vs CSKA MOSCOW     2.3  2.75    2.85
  10    RAPID VIENNA vs CELTIC          1.6 2.3 2.3
  11    SPARTAK MOSCOW vs FK VOZDOVAC   2.3 3.2 6.75
  21    LUDOGORETS vs DINAMO MOSCOW     1.2 2.35    2.6
  31    FC THUN vs FC ZURICH            2.9 2.8 2.6
  42    FC NURNBERG vs BOHEMIANS 1905   2.45    2.9 4.6
  53    LILLESTROM vs STROMSGODSET       3.0    2.5 3.35
  64    HALMSTAD vs IF ELFSBORG         2.4 2.5 2.0
  75    FLANDRIA vs CHACARITA JUNIORS   1.78    1.67    2.54
  86    CA RIVER PLATE vs DANUBIO       1.25    1.15    2.7
  97    JUVENTUD vs CERRO               1.55    1.15    2.3

`

是否可以执行单个关节手术

这些表中存在的数据具有相同的团队值，但在HOME，DRAW和AWAY上的值不同，所以我想要的是执行此规范的sql语法。

它应将home表中myshop的值除以1，将draw表中myshop2的值除以1，将away表中myshop3的值除以1，然后将所有值求和为每个团队获取一个值，并且输出应与相应的团队合计，如下面的简单示例所示。

FK PARTIZAN vs CSKA MOSCOW 105

理想语法应该像

SELECT from MYSHOP(home/1)+MYSHOP2(draw/1)+MYSHOP3(away/1) where TEAMS(MYSHOP=MYSHOP2=MYSHOP3)

需要您对此的想法。

最佳答案

恐怕您的语法愿望和语法现实不匹配。

我真的不知道您要达到什么目的，但是依靠诸如SPARTAK MOSCOW vs FK VOZDOVAC之类的字符串来连接不太可能很好。例如，这些“加入”应该：

莫斯科斯巴达克vs伏兹多瓦克
FK沃兹多瓦克vs斯帕塔克·莫斯科

考虑将其作为合并这三个表的一种方式：

      SELECT
            1 tbl_src, id_no, teams, home, draw, away
      FROM myshop
      UNION ALL
      SELECT
            2 tbl_src, id_no, teams, home, draw, away
      FROM myshop2
      UNION ALL
      SELECT
            3 tbl_src, id_no, teams, home, draw, away
      FROM myshop3

试试这个：

SELECT
      teams
    , SUM(home)
    , SUM(draw)
    , SUM(away)
FROM (
      SELECT
            1 tbl_src, id_no, teams, home, draw, away
      FROM myshop
      UNION ALL
      SELECT
            2 tbl_src, id_no, teams, home, draw, away
      FROM myshop2
      UNION ALL
      SELECT
            3 tbl_src, id_no, teams, home, draw, away
      FROM myshop3
) AS d
GROUP BY
      teams