SELECT DISTINCT的替代 [英] Alternative to SELECT DISTINCT

问题描述

我对SQL查询不太熟悉，但是注意到使用Select Distinct运行查询时性能会显着下降。我正在运行SQL Server 2008 R2。以下是我的查询：

I'm not too familiar with SQL queries but have noticed a significant performance decrease when running a query using Select Distinct. I'm running SQL Server 2008 R2. Below is my query:

select distinct CL.ClientID, NL.Name 
from CL CL 
inner join PR PR on CL.ClientID = PR.ClientID 
where PR.WBT1 in (Select distinct WBT1 
                  from TabFields 
                  where custInclude = 'Y' and WBT2 = '') 
and PR.WBT2 = '' 
order by NL.Name

有人知道如何修改此查询而不使用select unique以便在返回相同结果时加快查询速度？任何帮助是极大的赞赏。谢谢。

Does anyone know how to revise this query without using select distinct in order to speed up the query while returning the same results? Any help is greatly appreciated. thanks.

推荐答案

由于加入，您只需要DISTINCT。

You only need DISTINCT because of the JOIN.

因此，不要使用JOIN：使用EXISTS并将所有您实际上没有选择的表推入EXISTS子句中

So don't use a JOIN: use EXISTS and push all tables that you don't actually SELECT from into the EXISTS clause

select CL.ClientID, CL.Name 
from CL CL 
WHERE EXISTS (SELECT *
   FROM
      PR PR 
      JOIN
      TabFields TF ON PR.WBT1 = TF.WBT1
   WHERE
      PR.WBT2 = '' AND
      TF.custInclude = 'Y' and TF.WBT2 = '' AND
      CL.ClientID = PR.ClientID
      )
order by CL.Name

这篇关于SELECT DISTINCT的替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！