当Distinct和Group By的表现不同时？ [英] When the performance of Distinct and Group By are different?

问题描述

我知道在简单的查询中，Distinct和Group By的表现和执行计划几乎是一样的。

eg

  SELECT Name FROM NamesTable GROUP BY名称
 SELECT DISTINCT名称FROM NamesTable 
  

但是我已经在某些情况下看过他们的表现会有所不同，例如在子查询中，等等？

那么，你可以举一些例子，或者解释一些他们的表现不同的场景吗？

非常感谢

解决方案

如果您在字段列表中包含计算值，您将看到执行计划中的差异。 / p>

 选择Value，
 getdate（）
 from YourTable 
按UnitID 
 
从YourTable 
中选择不同的
值，
 getdate（）
 
 
 
 
在计算标量值之前，组由查询聚合。  distinct 查询计算聚合之前的标量值。  
I know in simple queries the performance and execution plans of the Distinct and Group By are almost the same.

e.g.
SELECT Name FROM NamesTable GROUP BY Name
SELECT DISTINCT Name FROM NamesTable
But I've read in some scenarios their performance would be different e.g. in subqueries, etc?

So, could you make some examples or explain some scenarios where their performance are different?

Many thanks
解决方案
If you include a calculated value in the field list you will see a difference in the execution plan.
select Value,
       getdate()
from YourTable
group by UnitID

select distinct
       Value,
       getdate()
from YourTable
The group by query aggregates before it computes the scalar value. The distinct query computes the scalar value before the aggregate. 

                        
这篇关于当Distinct和Group By的表现不同时？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！