设为首页 收藏本站
查看: 1481|回复: 0

[经验分享] mysql group by用法

[复制链接]

尚未签到

发表于 2018-10-3 12:07:56 | 显示全部楼层 |阅读模式
  1、
  group by 用法解析
  group by语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。
  SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。
  某个员工信息表结构和数据如下:

  >  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代码

  •   select columnA,columnB from table group by columnA,columnB
  [sql] view plaincopy

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

  •   select columnA,columnC from table group by columnA
  [sql] view plaincopy

  •   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代码

  •   select year,month from magazine group by year,month order by year desc,month desc
  [sql] view plaincopy

  •   select year,month from magazine group by year,month order by year desc,month desc
  那具体的期数信息就丢了?能不能做group by的时候,还能返回在某个年份year和月份month分组下的所有期数volumn信息?(某个年份+月份下的期数信息是不固定的,只能通过数据库查询才能获得)
  该是GROUP_CONCAT上阵的时候了。
  Sql代码

  •   select year,month GROUP_CONCAT(volumn) from magazine group by year,month order by year desc, month desc
  [sql] view plaincopy

  •   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代码

  •   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

  •   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代码

  •   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

  •   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代码

  •   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

  •   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


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-609402-1-1.html 上篇帖子: mysql binlog解析 下篇帖子: Mysql的loadfile()常见用法
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表