mysql - 获取两个表的总和 GROUP BY date in year

Question

我正在做一个捐赠报告，我有两个表 tbldonation 和 tblpubdonation我想按年得到他们的总和。

tbldonation:
amount      |      received
100         :      2016-01-02 08:42:20
100         :      2015-12-01 09:20:00

tblpubdonation:
amount      |      received
100         :      2015-12-22 09:20:00

我的查询是:

SELECT * from
(
    (SELECT YEAR(received) as YY, sum(amount) as AMT FROM tbldonation)
    UNION ALL
    (SELECT YEAR(received) as YY, sum(amount) as AMT FROM tblpubdonation)
) results
WHERE results.YY <= Curdate()
GROUP BY results.YY
ORDER BY results.YY DESC

我得到了一个结果，但它不准确。应该是

YY        |       AMT
2016      :       100
2015      :       200

但我的结果是:

YY        |       AMT
2016      :       200
2015      :       100

值放错地方了。

Answer 1

尝试以下 MySql(因为 MySQL lacks support for FULL JOIN 。我们必须模拟它)

    SELECT  Yr , SUM(amountSum)
    FROM    ( SELECT    SUM(ISNULL(t1.amount, 0)) AS amountSum ,
                t1.year AS Yr
      FROM      tbldonation t1
      GROUP BY  t1.YEAR
      UNION ALL
      SELECT    SUM(ISNULL(t2.amount, 0)) AS amountSum ,
                t2.year AS Yr
      FROM      tblpubdonation t2
      GROUP BY  t2.YEAR
    ) Tbl
   GROUP BY Tbl.Yr

以下适用于 T-SQL

SELECT SUM (ISNULL(t1.amount,0)) + SUM(ISNULL(t2.amount,0)), ISNULL(t1.YEAR,t2.YEAR)
FROM dbo.tbldonation t1 
FULL OUTER JOIN dbo.tblpubdonation t2 ON t1.YEAR = t2.YEAR
GROUP BY t1.YEAR, t2.YEAR