MySQL-GROUP BY和DISTINCT有什么区别? [英] MySQL - What is the difference between GROUP BY and DISTINCT?

问题描述

可能重复:
Group By和Distinct之间是否有任何区别

MySQL查询中GROUP BY和DISTINCT有什么区别?

解决方案

重复的

GROUP BY和DISTINCT之间是否有任何区别

已经在这里讨论了

如果仍然想在这里听

井然有序的分组有其自己的用途.

Distinct用于从满足查询条件的记录中过滤出唯一记录.

Group by子句用于对在其上触发聚合函数并根据group by子句中的列返回输出的数据进行分组.它有其自身的局限性，例如除了聚合函数外，select查询中的所有列都必须是Group by子句的一部分.

因此，即使您可以拥有由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

可以写为

select distinct col1,col2,col3,col4,col5,col6,col7,col8,col9 from table

这会让你的生活，当你在选择列表中更多的列更加容易.但是同时，如果您需要在上面的列中同时显示sum(col10)，则必须使用Group By.在这种情况下，distinct将不起作用.

例如

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

希望这会有所帮助.

Possible Duplicate:
Is there any difference between Group By and Distinct

What's the difference between GROUP BY and DISTINCT in a MySQL query?

解决方案

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.

Distinct is used to filter unique records out of the records that satisfy the query criteria.

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.

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

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.

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.

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