GROUP BY子句
在数据库查询中，分组是一个非常重要的应用。分组是指将数据表中的记录以某个或者某些列为标准，划分为一组。
语法格式
GROUP BY { column_name | expression } [ , ... ]使用方法
GROUP BY子句中的表达式可以包含FROM子句中表，视图的任何列，无论这些列是否出现在SELECT列表中。
GROUP BY子句对行进行分组，但不保证结果集的顺序。 要对分组进行排序，请使用ORDER BY子句。
GROUP BY后的表达式可以使用括号，如：group by (expr1, expr2)，或者 group by (expr1), (expr2)。 但不支持 group by (expr1, expr2), expr3 格式。
示例
查询各部门员工总数，按照section_id分组。--删除staffs表。 DROP TABLE IF EXISTS staffs;--创建staffs表。 CREATE TABLE staffs ( staff_id NUMBER(6) not null, first_name VARCHAR2(20), last_name VARCHAR2(25), email VARCHAR2(25), phone_number VARCHAR2(20), hire_date DATE, employment_id VARCHAR2(10), salary NUMBER(8,2), commission_pct NUMBER(2,2), manager_id NUMBER(6), section_id NUMBER(4), graduated_name VARCHAR2(60) );--插入数据。 INSERT INTO staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2200.00, null, 124, 50); INSERT INTO staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2400.00, null, 124, 50); INSERT INTO staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50); INSERT INTO staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 4000.00, null, 124, 50); INSERT INTO staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 4200.00, null, 124, 50); INSERT INTO staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 4400.00, null, 124, 50); INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID) VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', to_date('05-02-1998', 'dd-mm-yyyy'), 'IT_PROG', 4800.00, null, 103, 60); INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID) VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', to_date('07-02-1999', 'dd-mm-yyyy'), 'IT_PROG', 4200.00, null, 103, 60); INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID) VALUES (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', to_date('17-08-1994', 'dd-mm-yyyy'), 'FI_MGR', 12000.00, null, 101, 100); COMMIT;--查询各部门员工总数，按照section_id分组。 SELECT section_id, COUNT(staff_id) FROM staffs GROUP BY section_id ORDER BY section_id; SECTION_ID COUNT(STAFF_ID) ---------------------------------------- -------------------- 50 6 60 2 100 1 3 rows fetched.「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」关注作者【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。评论
