GROUP BY和DISTINCT之间是否有区别 [英] Is there any difference between GROUP BY and DISTINCT

问题描述

  SELECT c FROM myTbl GROUP BY C 
 
 code> 

结果与之相同：



  SELECT DISTINCT C FROM myTbl 
  

我很好奇，有什么不同的方法一个SQL引擎处理这个命令，还是他们真的是一回事？

我个人更喜欢不同的语法，但我确定它比其他任何东西都更习惯于使用。

编辑：这不是一个关于聚合的问题。了解 GROUP BY 与聚合函数的用法。

解决方案

<一个href =https://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct#164485> MusiGenesis 的回应在功能上是正确的关于你所说的问题; SQL Server足够聪明地认识到，如果您使用Group By而不使用任何聚合函数，那么您实际上的意思是Distinct - 因此它会生成一个执行计划，就好像您只是使用Distinct 。

然而，我认为重要的是要注意 Hank 的回应 - 骑士对待Group By和Distinct可能会导致一些有害的陷阱，如果你不小心。说这不是一个关于聚合的问题是不完全正确的，因为你在问两个SQL查询关键字之间的功能差别，其中一个意味着要与聚合和一个不是。

锤子有时可以用螺丝刀驱动，但是如果你有方便的螺丝刀，为什么要麻烦呢？

（...就此类推而言， Hammer：Screwdriver :: GroupBy：Distinct 和 screw = >获取表列中唯一值的列表）

I learned something simple about SQL the other day:

SELECT c FROM myTbl GROUP BY C

Has the same result as:

SELECT DISTINCT C FROM myTbl

What I am curious of, is there anything different in the way an SQL engine processes the command, or are they truly the same thing?

I personally prefer the distinct syntax, but I am sure it's more out of habit than anything else.

EDIT: This is not a question about aggregates. The use of GROUP BY with aggregate functions is understood.

解决方案

MusiGenesis' response is functionally the correct one with regard to your question as stated; the SQL Server is smart enough to realize that if you are using "Group By" and not using any aggregate functions, then what you actually mean is "Distinct" - and therefore it generates an execution plan as if you'd simply used "Distinct."

However,I think it's important to note Hank's response as well - cavalier treatment of "Group By" and "Distinct" could lead to some pernicious gotcha's down the line if you're not careful. It's not entirely correct to say that this is "not a question about aggregates" because you're asking about the functional difference between two SQL query keywords, one of which is meant to be used with aggregates and one of which is not.

A hammer can work to drive in a screw sometimes , but if you've got a screwdriver handy, why bother?

(... for the purposes of this analogy, Hammer : Screwdriver :: GroupBy : Distinct and screw => get list of unique values in a table column)

这篇关于GROUP BY和DISTINCT之间是否有区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！