聚合函数

1. 最大最小函数
   max和min是用来取某个字段的最大最小值，并不是说如果你有几个数，然后你直接从它们中取出最大或者最小，由于我不知道greatest和least这两个函数，最后我实现的是通过case去比较的，如果A大于B就取A，否则取B，现在就可以直接用greatest(A, B)来实现了。
2. coalescue
   返回表达式列表中第一个非空的值，列表必须都是相同类型，也可以是一个表的同一行、不同列的值进行比较。

SELECT coalesce(1,NULL,2) FROM dual; -- 返回1SELECT coalesce(NULL,2,1) FROM dual; -- 返回2SELECT coalesce(t.empno,t.mgr) FROM scott.emp t; -- 效果类似 NVL( t.empno, t.mgr )

1. greatest
   返回表达式列表中最大值，列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。当列表中有一个值为null，那么就返回null，这个地方需要注意，如果需要当值为null的时候，可以考虑用nvl转为0来进行比较。

SELECT greatest(1,3,2) FROM dual; -- 返回3SELECT greatest('A','B','C') FROM dual; -- 返回CSELECT greatest(NULL,'B','C') FROM dual; -- 返回nullSELECT greatest(t.empno,t.mgr) FROM scott.emp t; -- 返回empno和mgr 较大值

1. least
   返回表达式列表中最小值，列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。当列表中有一个值为null，那么就返回null，这个地方需要注意，如果需要当值为null的时候，可以考虑用nvl转为0来进行比较。

SELECT least(1,3,2) FROM dual; -- 返回1SELECT least('A','B','C') FROM dual; -- 返回ASELECT least(NULL,'B','C') FROM dual; -- 返回nullSELECT least(t.empno,t.mgr) FROM scott.emp t; -- 返回empno和mgr 较小值

对于max和min函数，如果处理的列中有null值，那么max和min会忽略null值，但是，如果在该列中，所有行的值都是null，那么max、min的返回值就是null。

1. wm_concat
   If you are not running 11g Release 2 or above, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.
   Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.
   WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.Also, WM_CONCAT has been removed from 12c onward, so you can’t pick this option.

COLUMN employees FORMAT A50SELECT deptno, wm_concat(ename) AS employeesFROM   empGROUP BY deptno;    DEPTNO EMPLOYEES---------- --------------------------------------------------        10 CLARK,KING,MILLER        20 SMITH,FORD,ADAMS,SCOTT,JONES        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD3 rows selected.

1. LISTAGG
   The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

COLUMN employees FORMAT A50SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM   empGROUP BY deptno;    DEPTNO EMPLOYEES---------- --------------------------------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

1. NVL和NVL2
   NVL ( expr1 , expr2 ): If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
   NVL2 ( expr1 , expr2 , expr3 ): If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2