"not in"不在唯一列上的 MySql 查询

Question

我在MySQL数据库中有两个这样的表：

Table readings:
reading_id, building_id, sub_id, reading_date
Table billings:
billing_id, building_id, sub_id, date_start, date_end

这就是它的工作原理：对于每对夫妇building_id，sub_id在阅读表上，我有几个阅读日期。

其中一些读数日期可用作账单表中的date_start或date_end。我想要实现的是一个查询，它从读数表中返回building_id sub_id的几个，其中reading_date从未使用过，也没有作为date_start也没有作为date_end的building_id/sub_id。

由于建筑表很大(>5k 行(，我必须同时检查所有建筑物，我想要最快的方法来执行此操作，而不是进行 5k 查询。

我试过这个查询，但它不起作用：

SELECT DISTINCT a.reading_id, a.sub_id 
FROM readings AS a
    LEFT JOIN billings AS b ON b.building_id=a.building_id 
                            AND b.sub_id=a.sub_id
WHERE reading_date NOT IN (SELECT DISTINCT date_start 
                            FROM billings 
                            WHERE b.building_id=a.building_id 
                              AND b.sub_id=a.sub_id
                           )
AND reading_date NOT IN (SELECT DISTINCT date_end 
                         FROM billings 
                         WHERE b.building_id=a.building_id 
                           AND b.sub_id=a.sub_id
                        )

Answer 1

嗯。 NOT EXISTS通常比NOT IN快：

SELECT r.reading_id, r.sub_id
FROM readings r
WHERE NOT EXISTS (SELECT 1
                  FROM billings b
                  WHERE b.building_id = r.building_id AND
                        b.sub_id = r.sub_id AND
                        b.date_start = r.reading_date
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM billings b
                  WHERE b.building_id = r.building_id AND
                        b.sub_id = r.sub_id AND
                        b.date_end = r.reading_date
                 );

为了提高性能，您需要两个索引：billings(building_id, sub_id, date_start) 和 billings(building_id, sub_id, date_end) 。

笔记：

• 外部查询中的JOIN是不必要的。 NOT IN完成您想要的工作。
• 使用IN时不需要SELECT DISTINCT(尽管此代码已替换为EXISTS(。
• SELECT DISTINCT在外部查询中会产生不必要的开销。