根据t1 mysql，选择t2中不存在的行 [英] Select a row that doesn't exist in t2, based on t1 mysql

问题描述

我正在使用MySQL和PHP. 我有2张桌子.

I'm using MySQL and PHP. I have 2 tables.

第一个有日期. 该表称为dates

The first one has dates in it. This table is called dates

+-------+-------------+----------+---------+
|  id   |  unixTime   |  month   |  year   |
+------------------------------------------+
|  1    | 1443657600  |    10    |   2015  |
|       |             |          |         |
|  2    | 1443657600  |    11    |   2015  |
|       |             |          |         |
|  3    | 1443657600  |    12    |   2015  |
|       |             |          |         |
|  4    | 1443657600  |    01    |   2016  |
|       |             |          |         |
+-------+-------------+----------+---------+

如果您查询SELECT * FROM analytics WHERE clientfk=36，将显示下表.因为#36号客户在一月份停止成为客户，所以只有3个条目.

The table below will show if you query SELECT * FROM analytics WHERE clientfk=36. There's only 3 entries because client #36 stopped being a client in January.

此表称为analytics

+------------------------------------------------+
|  id       datefk          Value       clientfk |
+------------------------------------------------+
|  156        1          "some value"       36   |
|                                                |
|  157        2        "another value"      36   |
|                                                |
|  157        3         "thrid value"       36   |
|                                                |
+------------------------------------------------+

我想加入(?)这些表的union(?)并获得如下所示的输出:(NB，null值)

I want to join(?) union(?) these tables and get an output that looks like this: (NB, the null value)

+------------------------------------------------+
|  month      year      client      value        |
+------------------------------------------------+
|   10         2015       36      "Some value"   |
|                                                |
|   11         2015       36     "Another value" |
|                                                |
|   12         2015       36      "third value"  |
|                                                |
|   1          2016       36         NULL        |
+------------------------------------------------+

我尝试了以下查询，其中包括无数其他操作，但总是不返回任何内容或仅返回3行

I tried the query below, among a myriad of other things but always either returns nothing or just 3 rows

SELECT *
FROM analytics a
WHERE a.clientfk = 36
AND NOT EXISTS (SELECT null FROM dates d
                WHERE d.id = a.datefk)

推荐答案

您正在寻找LEFT JOIN

SELECT `dates`.`month`, `dates`.`year`, `analytics`.`clientfk` AS 'client', `analytics`.`value`
    FROM `dates`
        LEFT JOIN `analytics` ON `analytics`.`datefk` = `dates`.`id` AND `analytics`.`clientfk` = '36'
    WHERE 1

这篇关于根据t1 mysql，选择t2中不存在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！