MySQL - GROUP BY 和 DISTINCT 之间有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4477552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - What is the difference between GROUP BY and DISTINCT?
提问by sreenavc
Possible Duplicate:
Is there any difference between Group By and Distinct
What's the difference between GROUP BY and DISTINCT in a MySQL query?
MySQL 查询中的 GROUP BY 和 DISTINCT 之间有什么区别？
回答by Shakti Singh
Duplicate of
重复的
Is there any difference between GROUP BY and DISTINCT
It is already discussed here
这里已经讨论过了
If still want to listen here
如果还想听这里
Well group by and distinct has its own use.
好 group by 和 distinct 有它自己的用途。
Distinct is used to filter unique records out of the records that satisfy the query criteria.
Distinct 用于从满足查询条件的记录中过滤出唯一的记录。
Group by clause is used to group the data upon which the aggregate functions are fired and the output is returned based on the columns in the group by clause. It has its own limitations such as all the columns that are in the select query apart from the aggregate functions have to be the part of the Group by clause.
Group by 子句用于对触发聚合函数的数据进行分组，并根据 group by 子句中的列返回输出。它有其自身的限制，例如除了聚合函数之外，select 查询中的所有列都必须是 Group by 子句的一部分。
So even though you can have the same data returned by distinct and group by clause its better to use distinct. See the below example
因此，即使您可以通过 distinct 和 group by 子句返回相同的数据，最好使用 distinct。看下面的例子
select col1,col2,col3,col4,col5,col6,col7,col8,col9 from table group by col1,col2,col3,col4,col5,col6,col7,col8,col9
can be written as
可以写成
select distinct col1,col2,col3,col4,col5,col6,col7,col8,col9 from table
It makes you life easier when you have more columns in the select list. But at the same time if you need to display sum(col10) along with the above columns than you will have to use Group By. In that case distinct will not work.
It makes you life easier when you have more columns in the select list. 但同时，如果您需要将 sum(col10) 与上述列一起显示，则必须使用 Group By。在这种情况下， distinct 将不起作用。
eg
例如
select col1,col2,col3,col4,col5,col6,col7,col8,col9,sum(col10) from table group by col1,col2,col3,col4,col5,col6,col7,col8,col9
Hope this helps.
希望这可以帮助。
回答by Mark Byers
DISTINCTworks only on the entire row. Don't be mislead into thinking SELECT DISTINCT(A), Bdoes something different. This is equivalent to SELECT DISTINCT A, B.
DISTINCT仅适用于整行。不要被误导认为会SELECT DISTINCT(A), B做一些不同的事情。这相当于SELECT DISTINCT A, B.
On the other hand GROUP BYcreates a group containing all the rows that share each distinct value in a single column (or in a number of columns, or arbitrary expressions). Using GROUP BYyou can use aggregate functions such as COUNTand MAX. This is not possible with DISTINCT.
另一方面，GROUP BY创建一个组，其中包含在单个列（或在多个列或任意表达式中）共享每个不同值的所有行。使用GROUP BY您可以使用聚合函数，例如COUNT和MAX。这是不可能的DISTINCT。
- If you want to ensure that all rows in your result set are unique and you do not need to aggregate then use
DISTINCT. - For anything more advanced you should use
GROUP BY.
- 如果要确保结果集中的所有行都是唯一的并且不需要聚合，则使用
DISTINCT. - 对于任何更高级的东西，你应该使用
GROUP BY.
Another difference that applies only to MySQL is that GROUP BYalso implies an ORDER BYunless you specify otherwise. Here's what can happen if you use DISTINCT:
另一个仅适用于 MySQL 的区别是，除非您另外指定，否则这GROUP BY也意味着ORDER BY。如果您使用，可能会发生以下情况DISTINCT：
SELECT DISTINCT a FROM table1
Results:
结果：
2 1
But using GROUP BYthe results will come in sorted order:
但是使用GROUP BY结果将按顺序排列：
SELECT a FROM table1 GROUP BY a
Results:
结果：
1 2
As a result of the lack of sorting using DISTINCTis faster in the case where you can use either. Note: if you don't need the sorting with GROUP BYyou can add ORDER BY NULLto improve performance.
由于缺乏排序 usingDISTINCT在您可以使用的情况下更快。注意：如果您不需要排序 withGROUP BY您可以添加ORDER BY NULL以提高性能。
