MySQL 合并表，值为零

Question

表A名称为来源

ID    |   date   |  valueS   | commonID
1       26.8.14     Svalue01   11
2       21.8.14     Svalue02   11
3       25.8.14     Svalue03   11

B表名是目的地

ID    |   date   |  valueD   | commonID
1       26.8.14     Dvalue01   11
2       21.8.14     Dvalue03   11
3       24.8.14     Dvalue03   11

所以我目前正在使用

SELECT a.*, b.* FROM (SELECT * FROM Source WHERE commonID = '11')a JOIN destination b ON a.commonID = b.commonID

但这并没有让我得到想要的结果。

我想要按日期排序的东西，如果在日期上都没有记录，则一个为零。

示例它应该是什么样子

ID    |   date   |  valueD   | commonID | ID    |   date   |  valueS   | commonID
1       26.8.14     Dvalue01   11         1       26.8.14     Svalue01   11
                                          3       25.8.14     Svalue03   11
3       24.8.14     Dvalue03   11
2       21.8.14     Dvalue03   11         2       21.8.14     Svalue02   11

这是可能的吗？

附加信息:

-使用 Mysql 5.5.37 (MariaDB)-ID 在两者上都是主要的-日期字段是“时间戳”- 值字段是 INT-ID 字段是 INT-引擎是InnoDB

我希望我提供了足够的信息并尝试提出一个很好的解释性问题

谢谢你的帮助

Answer 1

你想在日期加入，因为那是决定列所以像这样

SELECT 
    COALESCE(s.id, "") as s_id,
    COALESCE(s.date, "") as s_date,
    COALESCE(s.valueS, "") as 'valueS',
    COALESCE(s.commonID, "") as s_commonID,
    COALESCE(d.id, "") as d_id,
    COALESCE(d.date, "") as d_date,
    COALESCE(d.valueD, "") as 'valueD',
    COALESCE(d.commonID, "") as d_commonID
FROM source s
LEFT JOIN destination d on d.date = s.date
      AND d.commonID = s.commonID
WHERE d.commonID = 11

UNION

SELECT 
    COALESCE(s1.id, "") as s_id,
    COALESCE(s1.date, "") as s_date,
    COALESCE(s1.valueS, "") as 'valueS',
    COALESCE(s1.commonID, "") as s_commonID,
    COALESCE(d1.id, "") as d_id,
    COALESCE(d1.date, "") as d_date,
    COALESCE(d1.valueD, "") as 'valueD',
    COALESCE(d1.commonID, "") as d_commonID
FROM source s1
RIGHT JOIN destination d1 on d1.date = s1.date
       AND d1.commonID = s1.commonID
WHERE d1.commonID = 11
ORDER BY s_date DESC, d_date DESC

DEMO