sql - 使用“NOT IN”子句查询删除一个表中的记录，以检查多个表。这个查询可以更简化吗？

Question

现在我正在使用以下查询

 string myQry = "DELETE FROM myMainTbl AS a WHERE
    a.id NOT IN(SELECT b.mainid FROM mySubTbl_1 AS b WHERE b.mainid = a.id GROUP BY b.mainid HAVING MAX(b.sttime) > @mydate)
    AND
    a.id NOT IN(SELECT c.mainid FROM mySubTbl_2 AS c WHERE c.mainid = a.id GROUP BY c.mainid HAVING MAX(c.sttime) > @mydate)
    AND
    a.id NOT IN(SELECT d.mainid FROM mySubTbl_3 AS d WHERE d.mainid = a.id GROUP BY d.mainid HAVING MAX(d.sttime) > @mydate)
   AND
   a.id NOT IN(SELECT e.mainid FROM mySubTbl_4 AS e WHERE e.mainid = a.id GROUP BY e.mainid HAVING MAX(e.sttime) > @mydate)
   AND
   a.id NOT IN(SELECT f.mainid FROM mySubTbl_5 AS f WHERE f.mainid = a.id GROUP BY f.mainid HAVING MAX(f.sttime) > @mydate)";

可以更简化吗？

标签： sqlms-access

Answer 1

我建议NOT EXISTS结束NOT IN。这看起来像：

NOT EXISTS (SELECT 1 FROM mySubTbl_1 AS b WHERE b.mainid = a.id AND b.sttime > @mydate)

如果在日期之后不存在匹配行，则最大值也不存在。

然后，您可以添加索引来优化此查询： mySubTbl_1(mainid, sttime).

根据您的查询，创建索引并使用：

DELETE FROM myMainTbl AS a
    WHERE NOT EXISTS (SELECT b.mainid FROM mySubTbl_1 AS b WHERE b.mainid = a.id AND b.sttime > @mydate) AND
          NOT EXISTS (SELECT c.mainid FROM mySubTbl_2 AS c WHERE c.mainid = a.id AND c.sttime > @mydate) AND
          NOT EXISTS (SELECT d.mainid FROM mySubTbl_3 AS d WHERE d.mainid = a.id AND d.sttime > @mydate) AND
          NOT EXISTS (SELECT e.mainid FROM mySubTbl_4 AS e WHERE e.mainid = a.id AND e.sttime > @mydate) AND
          NOT EXISTS (SELECT f.mainid FROM mySubTbl_5 AS f WHERE f.mainid = a.id AND f.sttime > @mydate)";