Difference between GROUP BY and DISTINCT, without aggregate functions

问题: What's the difference between DISTINCT and GROUP BY. I understand that DISTINCT removes replicated values and GROUP BY is often used with aggregate functions, but why is it...

问题:

What's the difference between DISTINCT and GROUP BY. I understand that DISTINCT removes replicated values and GROUP BY is often used with aggregate functions, but why is it that under the following code

    SELECT DISTINCT continent FROM world
    HAVING SUM(population) > 100000000;

only displays 1 continent and this:

    SELECT continent FROM world
    GROUP BY continent HAVING SUM(population) > 100000000;

Shows all each continent? I was doing question https://sqlzoo.net/wiki/SUM_and_COUNT. I'm trying to display each continent that has SUM(population) thats bigger than 100000000. Thanks guys.


回答1:

In MySql, one can't really trust the results of an aggregate function like SUM when you use DISTINCT.
Because with DISTINCT it's not actually grouping on the other columns.

Note that in other databases than MySql & relatives, that one wouldn't face such confusion. Because those would complain about fields missing in the GROUP BY.

To explain it more visually.

With this sample data (MySql):

drop table if exists tmp_table;
create temporary table tmp_table (col1 int, col2 char(1), col3 int);

insert into tmp_table (col1, col2, col3) values
(1,'A',1),(2,'A',2),(3,'A',3),
(4,'B',4),(5,'B',5),(6,'B',6);

Via DISTINCT and SUM :

select distinct col2, sum(col3) from tmp_table;

Returns :

col2 sum(col3)
---- ---------
A           21

Via GROUP BY :

select col2, sum(col3) from tmp_table group by col2;

Returns :

col2 sum(col3)
---- ---------
A            6
B           15

Then when we include the HAVING clause to those queries

... having sum(col3) > 10;

Then the first select will return the wrong result: ('A',21)
And the second select will return what you'd expect : ('B',15)

  • 发表于 2018-07-05 17:52
  • 阅读 ( 295 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除