如何从选择加入语句中删除结果

Question

如何从下面的“选择加入语句”中删除结果。

SELECT 
   t2.TransactionID
FROM
    TableA AS t1
INNER JOIN 
    TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
WHERE
    t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000'

Answer 1

我猜您想要的是过滤出不需要的结果，您可以在选择查询中使用“不在”，例如过滤掉'id1'和'id2'，就像以下内容：

Select t2.TransaationID from TableA as t1 INNER JOION TABLEB as t2 ON t1.TransactionID = t2.TransactionID where t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' and ''2011-12-30 00:00:00.0000000 
and t1.TransactionID not in ('id1','id2')

Answer 2

据我了解您的问题，您正在问如何从两个表中删除结果集。在SQL语句的上下文中，您不能。 SQL删除语句仅作用于单个表。您必须进行2次删除，首先删除“ Tablea”记录，然后再删除“ TableB”记录。

delete from tableA where transctionID in
(select transactionID from tableB where accountingDate between ...);

delete from TableB where accountingDate between ...;