将来自两个不同表的统计数据行添加为总行.SQL - Adding rows of stats from two different tables to make a total row. SQL

Adding rows of stats from two different tables to make a total row. SQL

我有两行曲棍球队赛季的统计数据。每个季节一个。我可以使用以下查询将过去两年的每个统计数据放入一个表中:

$result = $db->query("SELECT name, wins, losses, otl, pts, gf, ga FROM standings2014_2015 WHERE teamid = '".$_POST["teamid"]."'");
$result2 = $db->query("SELECT name, wins, losses, otl, pts, gf, ga FROM standings2015_2016 WHERE teamid = '".$_POST["teamid"]."'");

我正试图有第三行，将增加胜利和损失等。下面是我的两个尝试查询:

$query3 = "SELECT name, wins, losses, otl, pts, gf, ga FROM standings2014_2015 WHERE teamid = '".$_POST["teamid"]."'
           UNION ALL
           SELECT name, wins, losses, otl, pts, gf, ga FROM standings2015_2016 WHERE teamid = '".$_POST["teamid"]."'";
           $result3 = $db->query($query3);

这个返回正确的所有行数据，我只是不知道如何正确添加列的值。我想做一些像$wins = $row['standings2015_2016.wins'] + $row['standings2014_2015'];的东西，但我很确定这不会工作，所以我尝试了第二个查询，它根本不起作用。

$query2 = "SELECT standings2015_2016.wins, standings2014_2015.wins, standings2015_2016.losses, standings2014_2015.losses,
           standings2015_2016.otl, standings2014_2015.otl, standings2015_2016.pts, standings2014_2015.pts, standings2015_2016.gf, standings2014_2015.gf 
           standings2015_2016.ga, standings2014_2015.ga 
           FROM standings2014_2015 WHERE teamid = '".$_POST["teamid"]."'
           LEFT JOIN standings2015_2016
           ON standings2014_2015.teamid = standings2015_2016.teamid";

下面是我的两个表的结构: CREATE TABLE 'standings2015_2016' ('teamid' INTEGER PRIMARY KEY NOT NULL, 'confid' INTEGER, 'divid' INTEGER, 'name' TEXT, 'wins' INTEGER, 'losses' INTEGER, 'otl' INTEGER, 'pts' INTEGER, 'gf' INTEGER, 'ga' INTEGER)
如果你想知道我到底是什么意思，点击这个链接，点击其中一个团队在这里

希望我正确理解了你的要求:

"SELECT name, SUM(wins), SUM(losses), SUM(otl), SUM(pts), SUM(gf), SUM(ga) FROM
(SELECT name, wins, losses, otl, pts, gf, ga FROM standings2014_2015 WHERE teamid = '".$_POST["teamid"]."'
       UNION ALL
SELECT name, wins, losses, otl, pts, gf, ga FROM standings2015_2016 WHERE teamid = '".$_POST["teamid"]."')
GROUP BY name"

使用如下代码:

"SELECT t1.name as name, t1.wins+t2.wins as wins, t1.losses+t2.losses as losses,t1.otl+t2.otl as otl, t1.pts=t2.pts as pts,
t1.gf=t2.gf as gf, t1.ga=t2.ga as ga
FROM standings2014_2015 t1 
inner join standings2015_2016 t2 
on t1.teamid=t2.teamid 
WHERE teamid = '".$_POST["teamid"]."'

我终于让它工作了。我的解决方案最接近Neria的解决方案

"SELECT teamid, name, SUM(wins) wins, SUM(losses) losses, SUM(otl) otl, SUM(pts) pts, SUM(gf) gf, SUM(ga) ga 
        FROM ( 
                SELECT *  FROM standings2014_2015 
                UNION ALL 
                SELECT * FROM standings2015_2016
              ) standings2014_2015
        WHERE teamid = '".$_POST["teamid"]."'" ;

这几乎与Neria的解决方案完全相同，但是我需要在FROM()语句的外部添加"standings2014_2015"。