MySQL合并表，零值 [英] MySQL merge table, with zero vaules

问题描述

表名称是来源

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

但这并不能给我带来希望的结果.

But this dont get me the wished result.

我想要一些按日期排序的东西，如果该日期和日期都没有记录，则一个为零.

i want something sorted by date, and if there is no record for both on the date, one is zero.

示例外观

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都是主要的-date字段为时间戳"-value字段是INT-ID字段为INT-Engine是InnoDB

-Using Mysql 5.5.37 (MariaDB) -ID is primary on both -date fields are "timestamp" -value fields are INT -ID fields are INT -Engine is InnoDB

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

I hope i provided enough information and tried to make a good explained question

感谢您的帮助

推荐答案

您想在日期加入，因为那是决定性的列，所以类似这样的

you want to join on the date as that is the determining column so something like this

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

演示

这篇关于MySQL合并表，零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！