我正在使用MySQL和PHP。我有2张桌子。
第一个有日期。此表称为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
。只有 3 个条目,因为客户端 #36 在 1 月份不再是客户端。
此表称为analytics
+------------------------------------------------+
| id datefk Value clientfk |
+------------------------------------------------+
| 156 1 "some value" 36 |
| |
| 157 2 "another value" 36 |
| |
| 157 3 "thrid value" 36 |
| |
+------------------------------------------------+
我想加入(?)联合(?)这些表并获得如下所示的输出:(注意,null
值)
+------------------------------------------------+
| month year client value |
+------------------------------------------------+
| 10 2015 36 "Some value" |
| |
| 11 2015 36 "Another value" |
| |
| 12 2015 36 "third value" |
| |
| 1 2016 36 NULL |
+------------------------------------------------+
我尝试了下面的查询,在无数其他事情中,但总是要么不返回任何内容,要么只返回 3 行
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
您将通过左连接获得结果。
SELECT
d.month as month, d.year as year, a.clientfk as client, a.value as value
from dates d
LEFT JOIN analytics a on d.id = a.datefk
WHERE a.clientfk = 36
这样,您将得到客户端也为空。因为分析中没有日期 ID 4 的数据。
+------------------------------------------------+
| month year client value |
+------------------------------------------------+
| 10 2015 36 "Some value" |
| |
| 11 2015 36 "Another value" |
| |
| 12 2015 36 "third value" |
| |
| 1 2016 NULL NULL |
+------------------------------------------------+
但是,如果您仍然希望客户端 ID 出现在该列中,则可以使用 case。
SELECT d.month as month, d.year as year, CASE
WHEN a.clientfk = null THEN 36
ELSE a.clientfk
END as client, a.value as value
from
dates d
LEFT JOIN analytics a on d.id = a.datefk
WHERE a.clientfk = 36