首先,group by是对表中数据进行分组,order by是对分组之后的结果进行查询。
group by与之过滤使用的是having字句,两者配套处理组。而where过滤的是记录(行)。
select cust_id,count(*) as orders from orders
group by cust_id having count(*) >= 2;
group by可以与order by、where、limit结合使用,其执行顺序是:where > group by > having > order by,书写顺序是group by + having + limit + order by 。
然而如果order by的字段中有一组存在多个值该怎么处理呢?下面举个栗子:
所建的测试数据表如下( 请无视id列 ):
create table t1( id integer(10) not null primary key,
col1 integer(10) not null,
col2 integer(10) not null,
col3 integer(10) not null);
按行1和行3( 即行1和行3相同算一组 )分组返回每组行数和每组字段之和:
select count(*) as num,sum(col1) as sum1,
sum(col2) as sum2,sum(col3) as sum3
from t1 group by col1,col3;
此时第一组中col2中含有两个值( 2,3 )若此时按照col2对结果进行排序会是什么样的?
select count(*),sum(col1) as sum1,
sum(col2) as sum2,sum(col3) as sum3
from t1 group by col1,col3 order by col2 desc;
结果如下:
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'test.t1.col2' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by