mysql常用語句——GROUP BY和HAVING

InfoQ 2021-11-25 19:04:43
mysql 常用 group having

{"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://javamana.com/2021/11/20211125190256347f.html

  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