mysql - 如何按日期 "merge"两个结果集

Question

我正在尝试合并下面两个 SQL 查询的结果:

上传表格

SELECT DATE(`timestamp`), SUM(uploaded) AS total_upload FROM uploads;

date          total_download

2014-09-11    1000
2014-09-12    1000
2014-09-13    1000

下载表

SELECT DATE(`timestamp`), SUM(downloaded) AS total_download FROM downloads;

date          total_upload

2014-09-09    1000
2014-09-10    1000
2014-09-11    1000

我想得到以下结果，如果没有下载则显示空白结果，如果没有上传则显示空白结果:

date          total_download   total_upload

2014-09-09                     1000
2014-09-10                     1000
2014-09-11    1000             1000
2014-09-12    1000
2014-09-13    1000

我以为我可以在 download_date = upload_date 上离开加入表格，但显然没有上传的日子将会丢失。我将如何正确合并这些结果？

Answer 1

将FULL JOIN与COALESCE()结合使用。

SELECT COALESCE(u.date, d.date) AS date,
    u.total_upload AS total_upload,
    d.total_download as total_download
FROM (SELECT DATE(`timestamp`) AS date, SUM(uploaded) AS total_upload FROM uploads) as u
FULL OUTER JOIN (SELECT DATE(`timestamp`) AS date, SUM(downloaded) AS total_download FROM downloads) as d
    ON u.date = d.date

语法可能有问题；我对 MySQL 很生疏。

编辑:如果您想要完全空白，则需要替换:COALESCE(CAST(u.total_upload AS varchar),'') AS total_upload 或类似内容。但请注意，这实际上会将您的输出数据类型更改为字符。