mysql - 无法返回符合条件的记录

Question

我有一个表，其中包含匹配列表:

id        |  datetime 
 2864049     2019-04-03 00:00:00
 2864050     2019-04-03 00:00:00
 2864051     2019-04-03 00:00:00

本质上，我必须检查它是否存在以及是否需要更新，为此我这样做了:

SELECT mm.match_id
FROM (SELECT 2864049 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime UNION ALL 
      SELECT 2864050 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime UNION ALL 
      SELECT 2864051 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime
     ) mm LEFT JOIN
     `match` m
     on m.id = mm.match_id AND m.datetime <> mm.match_datetime
WHERE m.id IS NULL

问题是此查询不应返回任何结果，因为所有三个 match 都存在，并且 datetime 字段也等于 matches 在 mm 表中提供。

仅当日期时间不同或匹配不存在时，查询才需要返回结果。

我做错了什么？

Answer 1

将不同的日期移至 where 子句

现场测试:http://sqlfiddle.com/#!9/ee7ffb/1

CREATE TABLE `match`
    (`id` int, `datetime` datetime)
;

INSERT INTO `match`
    (`id`, `datetime`)
VALUES
    (2864049, '2019-04-03 00:00:00'),
    (2864050, '2019-04-03 00:00:00'),
    (2864051, '2019-04-03 00:00:00'),
    (2864052, '2019-04-03 00:00:00'),
    (2864053, '2019-04-03 00:00:00'),    
    (2864054, '2019-04-03 00:00:00')        
;



SELECT m.id, `datetime`
FROM 
`match` `m`
left join  
     (SELECT 2864049 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime UNION ALL 
      SELECT 2864050 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime UNION ALL 
      SELECT 2864051 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime union all
      SELECT 2864052 as match_id, CAST("2019-04-04 00:00:00" AS DATETIME) AS match_datetime union all
      SELECT 2864054 as match_id, CAST("2019-04-03 00:00:00" AS DATETIME) AS match_datetime  
     ) mm 
     on m.id = mm.match_id 
WHERE 
mm.match_id IS NULL -- not existing
or m.datetime <> mm.match_datetime -- move to where clause

输出:

|      id |             datetime |
|---------|----------------------|
| 2864052 | 2019-04-03T00:00:00Z |
| 2864053 | 2019-04-03T00:00:00Z |

尽管 2864052 有匹配的 id，但它还是出现，因为它的日期时间与 mm 的不同

2864053 显示为 mm 上不存在