mysql group by

1、

group by 用法解析
group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。
某个员工信息表结构和数据如下：
  id  name  dept  salary  edlevel  hiredate 
      1 张三 开发部 2000 3 2009-10-11
      2 李四 开发部 2500 3 2009-10-01
      3 王五 设计部 2600 5 2010-10-02
      4 王六 设计部 2300 4 2010-10-03
      5 马七 设计部 2100 4 2010-10-06
      6 赵八 销售部 3000 5 2010-10-05
      7 钱九 销售部 3100 7 2010-10-07
      8 孙十 销售部 3500 7 2010-10-06 
例如，我想列出每个部门最高薪水的结果，sql语句如下：
SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
查询结果如下：
      DEPT  MAXIMUM 
      开发部 2500
      设计部 2600
      销售部 3500
解释一下这个结果：
1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY DEPT中包含的列DEPT。
2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。
注意：计算的是每个部门（由 GROUP BY 子句定义的组）而不是整个公司的 MAX(SALARY)。
例如，查询每个部门的总的薪水数
SELECT DEPT, sum( SALARY ) AS total
FROM STAFF
GROUP BY DEPT
查询结果如下：
DEPT  total 
开发部 4500
设计部 7000
销售部 9600
将 WHERE 子句与 GROUP BY 子句一起使用
分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句。
例如，查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff
WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY DEPT, EDLEVEL
查询结果如下：
  DEPT  EDLEVEL  MAXIMUM 
      设计部 4 2300
      设计部 5 2600
      销售部 5 3000
      销售部 7 3500
注意：在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
GROUP BY子句对DEPT和EDLEVEL的每个唯一组合各返回一行。
在GROUP BY子句之后使用HAVING子句
可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。为此，在GROUP BY子句后面包含一个HAVING子句。HAVING子句可包含一个或多个用AND和OR连接的谓词。每个谓词将组特性（如AVG(SALARY)）与下列之一进行比较：
例如：寻找雇员数超过2个的部门的最高和最低薪水：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING COUNT( * ) >2
ORDER BY DEPT
查询结果如下：
  DEPT  MAXIMUM  MINIMUM 
      设计部 2600 2100
      销售部 3500 3000
例如：寻找雇员平均工资大于3000的部门的最高和最低薪水：
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING AVG( SALARY ) >3000
ORDER BY DEPT
查询结果如下：
  DEPT  MAXIMUM  MINIMUM 
      销售部 3500 3000

 

2、

 mysql在介绍group by 和 having 子句前，我们必需先讲讲sql语言中一种特殊的函数：聚合函数， 例如s 在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数：聚合函数， 
例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。 

SELECT SUM(population) FROM bbc 

这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有 
国家的总人口数。 


通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。 
当你指定 GROUP BY region 时， 属于同一个region（地区）的一组数据将只能返回一行值． 
也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值． 

HAVING子句可以让我们筛选成组后的各组数据． 
WHERE子句在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前． 
而 HAVING子句在聚合后对组记录进行筛选。 

让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句，还采用第三节介绍的bbc表。 

SQL实例： 

一、显示每个地区的总人口数和总面积． 
SELECT region, SUM(population), SUM(area) 
FROM bbc 
GROUP BY region 

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中 
的不同字段（一或多条记录）作运算。 

二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。 

SELECT region, SUM(population), SUM(area) 
FROM bbc 
GROUP BY region 
HAVING SUM(area)>1000000 

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。 
相反，HAVING子句可以让我们筛选成组后的各组数据．

 

3、mysql中GROUP BY结合GROUP_CONCAT的使用

 

我们知道，group by可以将sql查询结果按照group by后面列进行分类显示。比如：

Sql代码

1. select columnA,columnB from table group by columnA,columnB  

[sql] view plaincopy

1. select columnA,columnB from table group by columnA,columnB  

 则查询结果将按照columnA和columnB分类显示。没有显示在group by中的列不能直接作为返回列放在sql语句中，比如如下sql就是不正确的

Sql代码

1. select columnA,columnC from table group by columnA   

[sql] view plaincopy

1. select columnA,columnC from table group by columnA   

 由于columnC不在group by的范围之类，所以这样写是不对的，所幸的是，group by支持一些sql 函数的使用，比如SUM,AVG,COUNT等等。这些都比较常用，今天我要记录下的是这个不常用的GROUP_CONCAT。

 

有一个需求，需要用到group by 才能实现，可是，我同是还需要返回某列的所有结果，（注意，不是做avg，sum等操作，我要枚举这列的所有结果），那么就可以用到GROUP_CONCAT。

举个例子：

我有一张数据库表结构如下：

列名含义year年份month月份volumn期数

该表存储了某杂志的年份，月份和期数。如果需求对该表内容作如下显示：

 

2010年12月第1期  第2期  第3期 第4期2010年11月第1期  第2期  第3期 第4期  第5期2010年10月第1期  第2期  第3期 第4期2010年9月第1期  第2期  第3期 第4期  第5期2010年8月第1期  第2期  第3期 第4期 

 

sql该怎么写呢？按照年份和月份做group by？然后按照年份和月份做倒叙排列？

Sql代码

1. select year,month from magazine group by year,month order by year desc,month desc  

[sql] view plaincopy

1. select year,month from magazine group by year,month order by year desc,month desc  

 那具体的期数信息就丢了？能不能做group by的时候，还能返回在某个年份year和月份month分组下的所有期数volumn信息？（某个年份+月份下的期数信息是不固定的，只能通过数据库查询才能获得）

该是GROUP_CONCAT上阵的时候了。

Sql代码

1. select year,month GROUP_CONCAT(volumn) from magazine group by year,month order by year desc, month desc  

[sql] view plaincopy

1. select year,month GROUP_CONCAT(volumn) from magazine group by year,month order by year desc, month desc  

 这样，查询的返回结果类似于：

yearmonthGROUP_CONCAT(volumn)2010121,2,3,42010111,2,3,4,5

 

不错吧？

还有点问题需要补充下，就是作为GROUP_CONCAT函数参数的字段，如过返回值为string，则上面的sql语句已经没有问题，但是如果是number，则返回的GROUP_CONCAT(volumn)值为BLOB类型（其实上面例子返回的就是一个blob类型，我只是为了演示的方便），需要做一下转化。

Sql代码

1. select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 )) from magazine group by year,month order by year desc, month desc  

[sql] view plaincopy

1. select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 )) from magazine group by year,month order by year desc, month desc  

 上面的sql对volumn做了一个从8进制到10进制的转换，这样返回的就是一个字符串了。

mysql默认会以‘,’来分隔多的值，如果想用其他的分隔符来分隔返回结果，比如期望返回值是这样的：1|2|3|4

这可以用SEPARATOR来搞定。

Sql代码

1. select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 ) SEPARATOR '|') from magazine group by year,month order by year desc, month desc   

[sql] view plaincopy

1. select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 ) SEPARATOR '|') from magazine group by year,month order by year desc, month desc   

更牛的是，你甚至可以对返回的volumn进行排序！！

Sql代码

1. select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 ) order by volumn desc SEPARATOR '|') from magazine group by year,month order by year desc, month desc  

[sql] view plaincopy

1. select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 ) order by volumn desc SEPARATOR '|') from magazine group by year,month order by year desc, month desc  

 

这个不常用的东东，还是比较好用的。