sql - 使OR查询更加简化

我正在尝试通过customerType进行搜索，但遇到了一个小问题：

SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields
WHERE  CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 241)

只需不到一秒钟即可运行，因此：

SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields
WHERE CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 240)

但是，当我尝试使用OR一次查找两种类型时：

SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE
CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 241)
Or CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 240)

大约需要40秒。

有没有更好的方法可以执行此操作，或者我缺少什么？
有关更多背景，请参见父问题：Displaying Query Results Horizontally

最佳答案

为什么不按以下方式重组OR查询：

SELECT DISTINCT CustomerID, CustomerName, City, State, Zip
  FROM qrySearchFields
 WHERE CustomerID IN (SELECT CustomerID
                        FROM tblCustomerTypeLineItems
                       WHERE CustomerTypeID IN (241, 240))

如果您使用的是SQL Server 2005及更高版本，则可以使用通用表表达式（CTE）：

WITH cteCustomerId AS
(
    SELECT CustomerID
      FROM tblCustomerTypeLineItems
     WHERE CustomerTypeID IN (241, 240)
)
SELECT DISTINCT CustomerID, CustomerName, City, State, Zip
  FROM qrySearchFields
 WHERE CustomerID IN (SELECT CustomerID
                        FROM cteCustomerId);