group by

 

group by语句的作用在于限制聚集函数的作用范围，比如max(字段)，如果不加group by语句，求出的是所有记录的最大值，而加上group by语句，max的作用范围就局限于相同字段的所有记录，求出的是这“一簇”记录的最大值。

但是group by 语句在使用的时候，要注意一些问题：

首先创建如下的表，并插入数据：

CREATE TABLE order VALUES(
 ordernumber varchar(255),//订单号
 years varchar(255),//年份
 amount double//销售额
)

INSERT INTO order values(1,2000,200);
INSERT INTO order values(2,2000,250);
INSERT INTO order values(3,2001,300);
INSERT INTO order values(4,2000,350);

现在的需求是：找出年度销售额最大的订单信息，最终的结果应该是

2 2000 250

4 2000 350

如何用sql语句实现这个需求呢：

select ordernumber,year,max(amount) from order group by years

这个sql语句会报错，原因在于如果按照years来分组，每一年对应一条结果记录，但是每个ordernumber可能对应多条结果记录，相当于一个theyear对应多个ordernumber。因此无法通过语法检查（这里不考虑max）。

如下的嵌套式sql语句呢：

SELECT * FROM order WHERE amount in (SELECT MAX(amount) FROM order)

也是错的，考虑这样一种情况：

我们再插入一条记录：

insert into table order values(5,2001,250)

这样就会错误地把第五条记录也选择了出来。因为max集合里包含250和350，第五条记录的amount恰为250，但是年份却是2001，在那一年它不是最大的。

 

正确的select姿势是这样的：

SELECT * FROM `order` r WHERE r.amount = (SELECT MAX(amount) FROM  `order` WHERE years = r.years GROUP BY years)

这是一个相关嵌套子查询。也就是说子查询的执行要依赖于父查询的执行。外层的父查询提供年份信息，内层的子查询需要这个年份信息作为查询的条件，查询出特定年份的最大值，把这个最大值返回给父查询，父查询又以此作为查询的条件，看我的amount是否最大。

 

经过测试，这可以得到正确的结果，但是否性能足够好呢？

分析这个sql语句，发现每次外层的父查询执行，都会在相应的年份中算出一个最大值，相当于要把这个年份的所有记录都要扫描一遍。

可以考虑另建一张表，一个字段是年份，一个字段是该年份amount的最大值。

CREATE TABLE maxYearAmount AS (SELECT years,MAX(amount) AS maxAmount FROM `order` GROUP BY years)

这样查询语句变为：

SELECT ordernumber,a.years,a.amount FROM `order` a JOIN maxYearAmount b ON a.years = b.years 

WHERE a.amount = b.maxAmount;

只需要一个join操作即可；所以，如果年份这个字段不同值的个数比较少，即maxYearAmount这个表体积比较小，是可以在原有基础上调高效率的。

 

另外一个问题是where子句和group by子句之间的顺序问题，正确的姿势是where在前，group by在后，而且不能颠倒。

SELECT MAX(amount) FROM `order` r WHERE r.years = '2001' GROUP BY years

 

另外一个问题是是否可以有双重分组，即group by同时按照两个字段来分组？答案是可以的

SELECT MAX(amount) FROM `order` GROUP BY ordernumber,years