mysql常用语句——GROUP BY和HAVING

InfoQ 2021-11-25 19:03:33
Mysql 常用 语句 常用语 用语

{"type":"doc","content":[{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"link","attrs":{"href":"http://www.netearn.top/rainNight/detail/33","title":"","type":null},"content":[{"type":"text","text":"mysql常用语句——GROUP BY和HAVING","attrs":{}}]}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"heading","attrs":{"align":null,"level":1},"content":[{"type":"text","text":"创建表结构","attrs":{}}]},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"create table `employ_info` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` char(20) NOT NULL DEFAULT '',\n `dept` char(20) NOT NULL DEFAULT '',\n `salary` varchar(255) NOT NULL DEFAULT '',\n `edlevel` int(11) NOT NULL DEFAULT 0,\n `hiredate` varchar(255) NOT NULL DEFAULT '',\n PRIMARY KEY(`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;\n","attrs":{}}]},{"type":"heading","attrs":{"align":null,"level":1},"content":[{"type":"text","text":"添加表内容","attrs":{}}]},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"INSERT INTO `employ_info` VALUES(1,'张三','开发部','2000','3','2009-10-11'),\n(2,'李四','开发部','2500','3','2009-10-11'),\n(3,'王五','设计部','2600','5','2010-10-02'),\n(4,'王六','设计部','2300','4','2010-10-03'),\n(5,'马七','设计部','2100','4','2010-10-05'),\n(6,'赵八','销售部','3000','5','2010-10-07'),\n(7,'钱九','销售部','3100','7','2010-10-07'),\n(8,'孙十','销售部','3500','7','2010-10-06');\n","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果。某个员工信息表结构和数据如下:","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"mysql> select * from employ_info;\n+----+--------+-----------+--------+---------+------------+\n| id | name | dept | salary | edlevel | hiredate |\n+----+--------+-----------+--------+---------+------------+\n| 1 | 张三 | 开发部 | 2000 | 3 | 2009-10-11 |\n| 2 | 李四 | 开发部 | 2500 | 3 | 2009-10-11 |\n| 3 | 王五 | 设计部 | 2600 | 5 | 2010-10-02 |\n| 4 | 王六 | 设计部 | 2300 | 4 | 2010-10-03 |\n| 5 | 马七 | 设计部 | 2100 | 4 | 2010-10-05 |\n| 6 | 赵八 | 销售部 | 3000 | 5 | 2010-10-07 |\n| 7 | 钱九 | 销售部 | 3100 | 7 | 2010-10-07 |\n| 8 | 孙十 | 销售部 | 3500 | 7 | 2010-10-06 |\n+----+--------+-----------+--------+---------+------------+\n8 rows in set (0.07 sec)\n","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"我想列出每个部门最高薪水的结果,sql语句如下:","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"mysql> select dept,max(salary) AS MAXIMUM from employ_info group by dept;\n+-----------+---------+\n| dept | MAXIMUM |\n+-----------+---------+\n| 开发部 | 2500 |\n| 设计部 | 2600 |\n| 销售部 | 3500 |\n+-----------+---------+\n3 rows in set (0.37 sec)\n","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"解释一下这个结果:1、 满足“SELECT子句中的列名必须为分组列或列函数”,因为SELECT有group by中包含的列dept;2、“列函数对于group by子句定义的每个组各返回一个结果”,根据部门分组,对每个部门返回一个结果,就是每个部门的最高薪水。将where子句与group by子句一起使用分组查询可以在形成组和计算列函数之前具有消除非限定行的标准where子句。必须在group by子句之前指定where子句","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"例如,查询公司2010年入职的各个部门每个级别里的最高薪水","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"mysql> select dept,edlevel,MAX(salary) AS MAXIMUM from employ_info group by dept,edlevel;\n+-----------+---------+---------+\n| dept | edlevel | MAXIMUM |\n+-----------+---------+---------+\n| 开发部 | 3 | 2500 |\n| 设计部 | 4 | 2300 |\n| 设计部 | 5 | 2600 |\n| 销售部 | 5 | 3000 |\n| 销售部 | 7 | 3500 |\n+-----------+---------+---------+\n5 rows in set (0.00 sec)\n\nmysql> select dept,edlevel,MAX(salary) AS MAXIMUM from employ_info where hiredate='2010-01-01' group by dept,edlevel;\nEmpty set (0.00 sec)\n\nmysql> select dept,edlevel,MAX(salary) AS MAXIMUM from employ_info where hiredate='2010-01-07' group by dept,edlevel;\nEmpty set (0.00 sec)\n","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"在SELECT语句中指定的每个列名也在GROUP BY子句中提到,未在这两个地方提到的列名将产生错误。GROUP BY子句对dept和edlevel的每个唯一组合各返回一行。","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"GROUP BY子句之后使用Having子句","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。因此,在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE(唯一的差别是WHERE过滤行,HAVING过滤组)AVING支持所有WHERE操作符。","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"例如,查找雇员数超过2个的部门的最高和最低薪水:","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"mysql> select dept,MAX(salary) as MAXIMUM,MIN(salary) as MINIMUM from employ_info group by dept having count(*)>2 order by dept;\n+-----------+---------+---------+\n| dept | MAXIMUM | MINIMUM |\n+-----------+---------+---------+\n| 设计部 | 2600 | 2100 |\n| 销售部 | 3500 | 3000 |\n+-----------+---------+---------+\n2 rows in set (0.09 sec)\n","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"例如,查找雇员平均工资大于3000的部门的最高薪水和最低薪水:","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"codeblock","attrs":{"lang":"bash"},"content":[{"type":"text","text":"mysql> select dept,max(salary) as MAXIMUM,MIN(salary) as MINIMUM from employ_info group by dept having avg(salary)>3000 order by dept;\n+-----------+---------+---------+\n| dept | MAXIMUM | MINIMUM |\n+-----------+---------+---------+\n| 销售部 | 3500 | 3000 |\n+-----------+---------+---------+\n1 row in set (0.00 sec)\n","attrs":{}}]},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null}},{"type":"paragraph","attrs":{"indent":0,"number":0,"align":null,"origin":null},"content":[{"type":"text","text":"博客来源:","attrs":{}},{"type":"link","attrs":{"href":"http://www.netearn.top","title":"","type":null},"content":[{"type":"text","text":"雨夜的博客","attrs":{}}]}]}]}
版权声明
本文为[InfoQ]所创,转载请带上原文链接,感谢
https://xie.infoq.cn/article/421cd49f64bbe3ec74a811924

  1. 图解 Kafka 线程模型及其设计缺陷
  2. Add data files for Oracle tablespaces or temporary tablespaces
  3. Intellij IDEA神器居然还有这些小技巧,mysql集群搭建视频
  4. IntelliJ IDEA(2019)之Web项目创建,掌门一对一java面试题
  5. InnoDB(2,如何访问Redis中的海量数据
  6. InheritableThreadLocal使用详解,java多线程面试题及答案整理
  7. How does Oracle modify the data type of a column
  8. Oracle 12C 12.1.0.1.0 management control file official document translation instructions
  9. Oracle 10g 10.2.0.1 in Oracle Linux 5.4 32bit RAC installation manual (Yimo Xiyang)
  10. Oracle 12C in Oracle Linux 6.5 64bit installation manual
  11. 一天十道Java面试题----第一天(面向对象-------》ArrayList和LinkedList)
  12. Schéma du modèle de fil Kafka et de ses défauts de conception
  13. Starting and shutting down Oracle RAC database cluster
  14. CRS_ Oracle CRS stack is already configured and will be running under init(1M)
  15. Common skills of Oracle stored procedure
  16. Check the number of CPUs, core books and threads of the Linux system
  17. jQuery-实例方法
  18. Oracle de duplicated data
  19. jQuery-dom和jQuery,入口函数(基本知识)
  20. Oracle creates unique constraints on columns that already have duplicate data
  21. JavaScript-拷贝
  22. JavaScript-this指向问题
  23.  There is ^ [[a garbled code problem in the up and down keys in Oracle sqlplus
  24. JavaScript-封装与继承(两种)
  25. JavaScript-包装类型
  26. JavaScript-传值(引用类型,基本类型)
  27. JavaScript-面向对象(构造函数,实例成员,静态成员)
  28. JavaScript-解构赋值
  29. JavaScript-箭头函数
  30. JavaScript-参数
  31. JavaScript-预解析(变量提升)
  32. JavaScript-闭包closure
  33. JavaScript-声明变量的关键字
  34. JavaScript - mot - clé pour déclarer une variable
  35. Fermeture de fermeture JavaScript
  36. JavaScript Pre - parse (promotion des variables)
  37. Paramètres JavaScript
  38. Fonction de flèche JavaScript
  39. JavaScript - déconstruction assignations
  40. Common annotations in springboot
  41. Building CentOS 7.6 with Linux
  42. JavaScript - orienté objet (constructeur, membre d'instance, membre statique)
  43. JavaScript value Transfer (reference type, Basic type)
  44. JavaScript - type d'emballage
  45. linux deepin/ubuntu安装flameshot火焰截图
  46. JavaScript - encapsulation et héritage (deux)
  47. JavaScript JS method for writing 99 multiplication table
  48. 從零開始學java - 第二十五天
  49. Apprendre Java à partir de zéro - jour 25
  50. Les voitures d'hiver, les voitures électriques et les voitures à essence ne sont pas les mêmes?
  51. JavaScript - ceci pointe vers le problème
  52. Copie JavaScript
  53. Spring boot quickly integrates swagger
  54. linux deepin/ubuntu安裝flameshot火焰截圖
  55. Capture d'écran de flamme de l'installateur de flamme Linux deepin / Ubuntu
  56. Jquery DOM et jquery, fonctions d'entrée (bases)
  57. Méthode d'instance jquery
  58. Méthode et démonstration de code dans l'interface de liste en Java
  59. Démarrage du Zookeeper
  60. Java oom Cognition