基本的聚合

聚合是一个非常实用的查询。

SELECT foo, MIN(bar) AS bar 
FROM tbl 
GROUP BY foo

在表tbl中，根据列foo分组，选择列foo及列bar中最小的值。

SELECT foo, MAX(bar) AS Count 
FROM tbl 
GROUP BY foo 
ORDER BY Count DESC;

在表tbl中，根据列foo分组，选择列foo及列bar中最大的值count，最按count降序排列。

也可以通过子查询来实现：

SELECT a.foo, a.bar 
FROM tbl a 
JOIN ( 
  SELECT foo, MAX(bar) AS Count 
  FROM tbl 
  GROUP BY foo 
) b ON a.foo=b.foo AND a.bar=b.count 
ORDER BY a.foo, a.bar;

MySQL引入了SQL扩展GROUP_CONCAT(),它简化了分组后列项的工作。例如，给出列suppliers和parts的表：

CREATE TABLE supparts(supID char(2),partID char(2)); 
INSERT INTO supparts VALUES 
('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s1','p5'),('s1','p6'), 
('s2','p1'),('s2','p2'),('s3','p2'),('s4','p2'),('s4','p4'),('s4','p5');

列出每个parts的suppliers:

SELECT partID,GROUP_CONCAT(supID ORDER BY supID) AS Suppliers  
FROM supparts  
GROUP BY partID; 

+--------+-------------+
| partID | Suppliers   |
+--------+-------------+
| p1     | s1,s2       |
| p2     | s1,s2,s3,s4 |
| p3     | s1          |
| p4     | s1,s4       |
| p5     | s1,s4       |
| p6     | s1          |
+--------+-------------+

当有数个表被join的时候，对于初学者来说，对于复杂的问题可能会感觉到无从下手。假设，对于这个schema，你被告知要检索"computer desk"销售最多的销售人员

drop table if exists salespersons, orders, orderlines, products; 
create table salespersons(salespersonid int,name char(8)); 
insert into salespersons values(1,'Sam'),(2,'Xavier'); 
create table orders(orderid int,salespersonid int); 
insert into orders values(1,1),(2,1),(3,1),(4,2),(5,2); 
create table orderlines(lineid int,orderid int,productid int,qty int); 
insert into orderlines values(1,1,1,1),(2,1,1,2),(3,2,2,1),(4,3,1,1),(5,4,1,1),(6,5,2,2); 
create table products(productid int,name char(32)); 
insert into products values(1,'computer desk'),(2,'lamp'),(3,'desk chair');

使用多表连接（join）：

select s.name, p.name, l.qty 
from salespersons s 
join orders       o using(salespersonid) 
join orderlines   l using(orderid) 
join products     p using(productid) 
+--------+---------------+------+ 
| name   | name          | qty  | 
+--------+---------------+------+ 
| Sam    | computer desk |    1 | 
| Sam    | computer desk |    2 | 
| Sam    | lamp          |    1 | 
| Sam    | computer desk |    1 | 
| Xavier | computer desk |    1 | 
| Xavier | lamp          |    2 | 
+--------+---------------+------+

现在， 我们需要筛选产品“computer desk“，加入聚合，找到销量最好的售货员：

select s.name, sum(l.qty) as n      -- sum quantities 
from salespersons s 
join orders       o using(salespersonid) 
join orderlines   l using(orderid) 
join products     p using(productid) 
where p.name='computer desk'     
group by s.name                     -- aggregate by salesperson 
order by n desc limit 1;            -- order by descending sum, pick off top value 
+------+------+ 
| name | n    | 
+------+------+ 
| Sam  |    4 | 
+------+------+

如果必须检索那些没有GROUP BY的列,且与分组表达式没有严格的1：1的关系，然后要避免返回非分组列的任意值，你可以把这些列加入到group by ，或者把GROUP BY 放在一个子查询中，然后与其他列join这些结果，例如：

SELECT s.partID, s, thiscol, s.thatcol, anothercol, x.Suppliers 
FROM supparts s 
JOIN ( 
  SELECT partID,GROUP_CONCAT(supID ORDER BY supID) AS Suppliers  
  FROM supparts  
  GROUP BY partID 
) x USING(partID)

如果sql_mode中包含ONLY_FULL_GROUP_BY，对于一个查询，选择了非聚合列，且没有函数依赖于分组列，MySQL会返回一个错误。

注：如果想知道sql_mode变量的值 ，可以用以下语句查询：

mysql> show variables like'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)