mysql - 在 MySQL 中，如果 A 列和 B 列的值不同，则获取数据

Question

我正在尝试编写一个查询来获取最近登录的客户，但我不确定为什么我没有得到任何结果!

lastActivity date is stored in customer table as well as in customer_statistics lastActivity

这是我的查询:

mysql> SELECT `customer`.`lastActivity`, `customer_statistics`.`lastActivity`,  `customer`.`lastActivity` = customer_statistics.lastActivity as todate
FROM (`customer`)
LEFT OUTER JOIN `customer_statistics` ON `customer`.`id` = `customer_statistics`.`customer`
WHERE `customer`.`lastActivity` = `customer_statistics`.`lastActivity` IN ('0', 'NULL')

这是数据的样子(在 where in 之前)，从这个列表中我只需要第 1 行和第 2 行。

+----+------------+------------+-------------+
| id | date 1     | date 2     | todate      |
+----+------------+------------+-------------+
|  1 | 2017-02-12 | 2017-02-13 | 0           |
|  2 | 2017-02-13 | NULL       | NULL        |
|  3 | 2017-02-15 | 2017-02-15 | 1           |
+----+------------+------------+-------------+

Answer 1

SELECT null IN (null) from DUAL; 返回 null。相反，您需要:

SELECT `customer`.`lastActivity`, `customer_statistics`.`lastActivity`,  `customer`.`lastActivity` = customer_statistics.lastActivity as todate
FROM (`customer`)
LEFT OUTER JOIN `customer_statistics` ON `customer`.`id` = `customer_statistics`.`customer`
WHERE `customer`.`lastActivity` != `customer_statistics`.`lastActivity` OR `customer`.`lastActivity` IS NULL OR `customer_statistics`.`lastActivity` IS NULL