Gauss 100 数据库函数介绍之聚集函数Part1
说明：
聚集函数之间不支持嵌套调用。
AVG
语法：
AVG(expr)
功能：所有输入值的均值（算术平均）。
示例：
返回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) );
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', 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 (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, null, 101, 10);SELECT AVG(MANAGER_ID) AS "AVG" FROM staffs; AVG ---------------------------------------- 116.333333333333333333333333333333333333 1 rows fetched.
COUNT
语法：
COUNT(expr)
功能：分组求个数。
示例：
返回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) );
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', 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 (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, null, 101, 10);SELECT COUNT(staff_ID) AS "COUNT" FROM staffS; COUNT -------------------- 3 1 rows fetched.
MAX
语法：
MAX(expr)
功能：分组求最大值。
示例：
返回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) );
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', 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 (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, null, 101, 10);SELECT MAX(section_ID) AS "MAX" FROM staffs; MAX ---------------------------------------- 50 1 rows fetched.
MIN
语法：
MIN(expr)
功能：分组求最小值。
示例：
返回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) );
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', 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 (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, null, 101, 10);SELECT MIN(staff_ID) AS "MIN" FROM staffs; MIN ---------------------------------------- 198 1 rows fetched.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69959250/viewspace-3127502/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
SQL参考-事务控制
请登录后发表评论
登录
全部评论
