哪一种比较好:不同的还是不同的?

Which is more efficient?

这是更有效率呢?

SELECT  theField
FROM    theTable
GROUP BY theField

或

SELECT  DISTINCT theField
FROM    theTable

7 个解决方案

#1

In your example, both queries will generate the same execution plan so their performance will be the same.

在您的示例中，两个查询都将生成相同的执行计划，因此它们的性能将是相同的。

However, they both have their own purpose. To make your code easier to understand, you should use distinct to eliminate duplicate rows and group by to apply aggregate operators (sum, count, max, ...).

然而，他们都有自己的目的。为了使代码更容易理解，您应该使用distinct来消除重复的行和分组，方法是应用聚合操作符(sum、count、max、…)。

#2

Doesn't matter, it results in the same execution plan. (at least for these queries). These kind of questions are easy to solve, by enabling query analyzer or SSMS to show the execution plan and perhaps the server trace statistics after running the query.

没关系，它会导致相同的执行计划。(至少对于这些查询)。通过启用查询分析器或SSMS来显示执行计划，或者在运行查询之后显示服务器跟踪统计信息，这些问题很容易解决。

#3

In most cases, DISTINCT and GROUP BY generate the same plans, and their performance is usually identical

在大多数情况下，通过生成相同的计划来区分和分组，并且它们的性能通常是相同的。

#4

You can check the Execution Plan to look for the total cost of this statements. The answer may vary in different scenarios.

您可以检查执行计划以查找此语句的总成本。答案可能在不同的场景中有所不同。

#5

Hmmm...so far as I can see in the Execution Plan for running similar queries, they are identical.

嗯…就我在运行类似查询的执行计划中看到的，它们是相同的。

#6

In MySQL, DISTINCT seems a bit faster than GROUP BY if theField is not indexed. DISTINCT only eliminate duplicate rows but GROUP BY seems to sort them in addition.

在MySQL中，如果没有对字段进行索引，那么DISTINCT看起来比GROUP要快一些。不同的只有消除重复的行，但是GROUP BY似乎对它们进行排序。

#7

-2

you do have to know the difference between the two and not use them interchangeably.

你必须知道两者之间的区别，不要交替使用它们。

#1