GROUP BY 和 DISTINCT 之间有什么区别吗 [英] Is there any difference between GROUP BY and DISTINCT

问题描述

前几天我学到了一些关于 SQL 的简单知识:

SELECT c FROM myTbl GROUP BY C

具有相同的结果:

SELECT DISTINCT C FROM myTbl

我很好奇，SQL 引擎处理命令的方式有什么不同，或者它们真的是一样的吗?

我个人更喜欢独特的语法，但我确信这比其他任何东西都更出于习惯.

这不是关于聚合的问题.了解 GROUP BY 与聚合函数的使用.

解决方案

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 gotchas 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屋！