SELECT DISTINCT 的替代方案

发布于 2024-11-16 10:41:08 字数 453 浏览 3 评论 0原文

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

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 的情况下修改此查询，以便在返回相同结果的同时加快查询速度？非常感谢任何帮助。谢谢。

原文

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半城柳色半声笛 2024-11-23 10:41:08

由于 JOIN，您只需要 DISTINCT。

因此，不要使用 JOIN：使用 EXISTS 并将所有实际上未从中 SELECT 的表推送到 EXISTS 子句中

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

You only need DISTINCT because of the JOIN.

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

回复收藏 0 原文

枕花眠 2024-11-23 10:41:08

您绝对不需要第二个SELECT DISTINCT。您可以将其替换为 EXIST：

select distinct CL.ClientID, NL.Name from CL CL 
inner join PR PR on CL.ClientID = PR.ClientID 
WHERE EXISTS 
(SELECT 1 from TabFields where WBT1=PR.WBS1 AND custInclude = 'Y' and WBT2 = '') 
and PR.WBT2 = '' order by NL.Name

并且我在 FROM /JOIN 中没有看到 NL。您错过了它还是应该是CL？

You definitely don't need the second SELECT DISTINCT. You can replace it by EXIST:

select distinct CL.ClientID, NL.Name from CL CL 
inner join PR PR on CL.ClientID = PR.ClientID 
WHERE EXISTS 
(SELECT 1 from TabFields where WBT1=PR.WBS1 AND custInclude = 'Y' and WBT2 = '') 
and PR.WBT2 = '' order by NL.Name

And I don't see NL in FROM /JOIN. Did you miss it or it's supposed to be CL?

回复收藏 0 原文

~没有更多了~