聚集函数不能在WHERE中,可以用在 HAVING 子句中

Question

聚集函数不能用在WHERE子句中,可以用在 HAVING 子句中，这是为什么呢？

Answer 1

逻辑上来说，where 子句的执行位于 group by 之前，where 子句执行时数据行还没有被分组，还不能使用 count 等函数

Answer 2

Oracle数据库的定制功能十分强大。Oracle不但允许用户定制自己的函数，还可以定制自己的聚集函数和分析函数。
　　本文将着重介绍使用自定义聚集函数建立一个字符串“sum”的示例：　　SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
　　2 STR VARCHAR2(30000),
　　3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE
　　(SCTX IN OUT T_LINK) RETURN NUMBER,
　　4 MEMBER FUNCTION ODCIAGGREGATEITERATE
　　(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
　　5 MEMBER FUNCTION ODCIAGGREGATETERMINATE
　　(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBE
　　R) RETURN NUMBER,
　　6 MEMBER FUNCTION ODCIAGGREGATEMERGE
　　(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
　　7 )
　　8 /
　　类型已创建。
　　SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
　　2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE
　　(SCTX IN OUT T_LINK) RETURN NUMBER IS
　　3 BEGIN
　　4 SCTX := T_LINK(NULL);
　　5 RETURN ODCICONST.SUCCESS;
　　6 END;
　　7
　　8 MEMBER FUNCTION ODCIAGGREGATEITERATE
　　(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
　　9 BEGIN
　　10 SELF.STR := SELF.STR || VALUE;
　　11 RETURN ODCICONST.SUCCESS;
　　12 END;
　　13
　　14 MEMBER FUNCTION ODCIAGGREGATETERMINATE
　　(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBE
　　R) RETURN NUMBER IS
　　15 BEGIN
　　16 RETURNVALUE := SELF.STR;
　　17 RETURN ODCICONST.SUCCESS;
　　18 END;
　　19
　　20 MEMBER FUNCTION ODCIAGGREGATEMERGE
　　(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
　　21 BEGIN
　　22 NULL;
　　23 RETURN ODCICONST.SUCCESS;
　　24 END;
　　25 END;
　　26 /
　　类型主体已创建。
　　SQL> CREATE OR REPLACE FUNCTION F_LINK
　　(P_STR VARCHAR2) RETURN VARCHAR2
　　2 AGGREGATE USING T_LINK;
　　3 /
　　函数已创建。
　　SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20));
　　表已创建。
　　SQL> INSERT INTO TEST VALUES (1, 'AAA');
　　已创建 1 行。
　　SQL> INSERT INTO TEST VALUES (2, 'BBB');
　　已创建 1 行。
　　SQL> INSERT INTO TEST VALUES (1, 'ABC');
　　已创建 1 行。
　　SQL> INSERT INTO TEST VALUES (3, 'CCC');
　　已创建 1 行。
　　SQL> INSERT INTO TEST VALUES (2, 'DDD');
　　已创建 1 行。
　　SQL> COMMIT;
　　提交完成。
　　SQL> COL NAME FORMAT A60
　　SQL> SELECT ID, F_LINK(NAME) NAME FROM TEST GROUP BY ID;
　　ID NAME
　　---------- ------------------------------------------------------
　　1 AAAABC
　　2 BBBDDD
　　3 CCC

Answer 3

上楼的谢谢，我还没学几天 ORACLE，只能看懂 CREATE OR REPLACE TYPE T_LINK 这句话和下面好部分。能不能再给我文字描述一下呀！

Answer 4

因为SQL的执行顺序为：先where 再group 再having 再select 后order
而聚合函数一般是对查询结果的计算
当执行完where的时候，啥结果都没有啊

Answer 5

Using HAVING with an Analytical Function 
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /Table created.SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /Table created.SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       48000,       'E')
  3  /1 row created.SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /1 row created.SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000,       58000,        'E')
  3  /1 row created.SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      36000,        'W')
  3  /1 row created.SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000,       53000,       'E')
  3  /1 row created.SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       85000,       'W')
  3  /1 row created.SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /1 row created.SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   'Painter');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   'Tester');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   'Dediator');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   'Chemist');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   'Accountant');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   'Manager');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   'Programmer');1 row created.SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   'Developer');1 row created.SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E7 rows selected.SQL> select * from job;
Hit a key to continue     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer8 rows selected.SQL>
SQL>
SQL> -- Using HAVING with an Analytical Function
SQL>
SQL> -- if a HAVING clause is added, it will have its effect just before the RANK.
SQL>
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
  2    MIN(orig_salary) minsalary
  3    --   RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
  4  FROM employee e, job j
  5  WHERE e.orig_salary < 43000
  6    AND e.empno = j.empno
  7  GROUP BY j.jobtitle
  8  HAVING MAX(orig_salary) > 34000
  9  ORDER BY j.jobtitle desc;no rows selectedSQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;Table dropped.SQL>
SQL> -- clean the table
SQL> drop table Employee;Table dropped.