使用某些值排除某些记录SQL Select

问题描述

我已经从下面的线程中使用了该解决方案(由AdaTheDev进行了处理)，因为它涉及到相同的问题:如何在sql select中排除具有某些值的记录

I've used the solution (by AdaTheDev) from the thread below as it relates to the same question:How to exclude records with certain values in sql select

但是，当将相同的查询应用于40,000多个记录时，查询将花费很长时间来处理(> 30分钟).还有另一种方法可以有效地查询具有某些值的某些记录(与上述stackoverflow线程相同的问题).我尝试在下面使用它，但仍然没有运气:

But when applying the same query to over 40,000 records, the query takes too long to process (>30mins). Is there another way that's efficient in querying for certain records with certain values (the same question as in the above stackoverflow thread). I've attempted to use this below and still no luck:

 SELECT StoreId
FROM sc
WHERE StoreId NOT IN (
  SELECT StoreId
  FROM StoreClients
  Where ClientId=5
);

谢谢-

推荐答案

您可以使用EXISTS:

SELECT StoreId
FROM sc
WHERE NOT EXISTS (
  SELECT 1
  FROM StoreClients cl
  WHERE sc.StoreId = cl.StoreId
  AND cl.ClientId = 5
  )

确保在StoreId列上创建索引.您的查询也可以从ClientId上的索引中受益.

Make sure to create an index on StoreId column. Your query could also benefit from an index on ClientId.

这篇关于使用某些值排除某些记录SQL Select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！