mysql - 连接具有不同行数的多个不同列上的两个表

Question

我有两个表:

temp 有 7027 行，架构如下所示

    +-------------+-------------+------+-----+---------+-------+
    | Field       | Type        | Null | Key | Default | Extra |
    +-------------+-------------+------+-----+---------+-------+
    | ip          | varchar(32) | YES  |     | NULL    |       |
    | month       | int(2)      | YES  |     | NULL    |       |
    | day         | int(2)      | YES  |     | NULL    |       |
    | hour        | int(2)      | YES  |     | NULL    |       |
    | totcount    | bigint(21)  | YES  |     | NULL    |       |
    +-------------+-------------+------+-----+---------+-------+

temp1 再次有 4972 行，架构如下所示

    +-------------+-------------+------+-----+---------+-------+
    | Field       | Type        | Null | Key | Default | Extra |
    +-------------+-------------+------+-----+---------+-------+
    | ip          | varchar(32) | YES  |     | NULL    |       |
    | month       | int(2)      | YES  |     | NULL    |       |
    | day         | int(2)      | YES  |     | NULL    |       |
    | hour        | int(2)      | YES  |     | NULL    |       |
    | compcount   | bigint(21)  | YES  |     | NULL    |       |
    +-------------+-------------+------+-----+---------+-------+

我想要做的是将 temp1 中的列 compcount 附加到 temp 中的数据，在没有 compcount 值的任何地方使用 0 或 null 值。 temp1 中存在的所有 ip、月、日、小时组合也存在于 temp 中，但反之则不然。

举个例子，如果我的温度如下:

    +----------------+-------+------+------+----------+
    | ip             | month | day  | hour | totcount |
    +----------------+-------+------+------+----------+
    | 0.0.0.0        |     1 |   17 |    0 |      215 |
    | 0.0.0.0        |     1 |   18 |    1 |      490 |
    | 0.0.0.0        |     1 |   19 |    0 |      749 |
    | 0.0.0.0        |     1 |   20 |    0 |      471 |
    | 0.0.0.0        |     1 |   21 |   15 |      330 |
    | 0.0.0.0        |     1 |   22 |   15 |       45 |
    | 0.0.0.0        |     1 |   23 |   14 |      214 |
    | 0.0.0.0        |     2 |   25 |    1 |       13 |
    | 1.1.1.1        |     1 |   17 |   21 |       58 |
    | 1.1.1.1        |     1 |   18 |   22 |       70 |
    | 1.1.1.1        |     1 |   20 |   22 |       89 |
    | 1.1.1.1        |     1 |   21 |   11 |       67 |
    +----------------+-------+------+------+----------+

和 temp1:

    +----------------+-------+------+------+----------+
    | ip             | month | day  | hour | compcount|
    +----------------+-------+------+------+----------+
    | 0.0.0.0        |     1 |   17 |    0 |      100 |
    | 0.0.0.0        |     1 |   18 |    1 |      176 |
    | 0.0.0.0        |     1 |   21 |   15 |      182 |
    | 0.0.0.0        |     1 |   22 |   15 |       36 |
    | 0.0.0.0        |     1 |   23 |   14 |      198 |
    | 1.1.1.1        |     1 |   17 |   21 |       46 |
    | 1.1.1.1        |     1 |   18 |   22 |       53 |
    | 1.1.1.1        |     1 |   20 |   22 |       27 |
    | 1.1.1.1        |     1 |   21 |   11 |       61 |
    +----------------+-------+------+------+----------+

那么我希望结果表是:

    +----------------+-------+------+------+----------+----------+
    | ip             | month | day  | hour | totcount |compcount |
    +----------------+-------+------+------+----------+----------+
    | 0.0.0.0        |     1 |   17 |    0 |      215 |      100 |
    | 0.0.0.0        |     1 |   18 |    1 |      490 |      176 |
    | 0.0.0.0        |     1 |   19 |    0 |      749 |        0 |
    | 0.0.0.0        |     1 |   20 |    0 |      471 |        0 |
    | 0.0.0.0        |     1 |   21 |   15 |      330 |      182 |
    | 0.0.0.0        |     1 |   22 |   15 |       45 |       36 |
    | 0.0.0.0        |     1 |   23 |   14 |      214 |      198 |
    | 0.0.0.0        |     2 |   25 |    1 |       13 |        0 |
    | 1.1.1.1        |     1 |   17 |   21 |       58 |       46 |
    | 1.1.1.1        |     1 |   18 |   22 |       70 |       53 |
    | 1.1.1.1        |     1 |   20 |   22 |       89 |       27 |
    | 1.1.1.1        |     1 |   21 |   11 |       67 |       61 | 
    +----------------+-------+------+------+----------+----------+

我尝试了查询

    select temp.ip, temp.month, temp.day, temp.hour, temp.totcount,
    temp1.compcount from temp1 left outer join temp on
    temp.ip=temp1.ip and temp.month=temp1.month and
    temp.day=temp1.day and temp.hour=temp1.hour order by ip;

但它只返回 4972 行。如何构建查询以返回我要查找的内容？

Answer 1

当您使用LEFT JOIN时，第一个表应该是包含所有行的表，第二个表是可能没有匹配项的表。

因此请更改表的顺序，或使用RIGHT JOIN。

select temp.ip, temp.month, temp.day, temp.hour, temp.totcount,
        temp1.compcount 
from temp 
left outer join temp1 on
    temp.ip=temp1.ip and temp.month=temp1.month and
    temp.day=temp1.day and temp.hour=temp1.hour 
order by ip, month, day, hour;

DEMO

如果您希望结果中使用 0 而不是 NULL，请使用 IFNULL(temp1.compcount, 0) AS compcount