oracle的一些函数(二)
5,1,5,89) min FROM DUAL ;
-- NULLIF逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
SELECT NULLIF(3,4),NVL(NULLIF('a','a'),'null') FROM DUAL ;
-- NVL2(c1,c2,c3)
SELECT NVL2(7,1,3) FROM DUAL ;
SELECT NVL2(NULL,1,3) FROM DUAL ;
-- SYS_CONNECT_BY_PATH(col,c)该函数只能应用于树状查询
SELECT SYS_CONNECT_BY_PATH(ename,'-->') FROM emp START WITH empno = 7369 CONNECT BY PRIOR MGR=EMPNO ;
-- SYS_CONTEXT(c1,c2[,n]) ：将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL ;
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL ;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL ;
SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL ;
-- AVG求平均值
SELECT ename,empno,AVG(sal) FROM emp GROUP BY empno,ename ;
-- COUNT
SELECT COUNT(*) FROM emp ;
-- MAX,MIN,SUM
SELECT MAX(sal) max,MIN(sal) min,SUM(sal) sum FROM emp ;
--DENSE_RANK(n1[,n2]...) WITHIN GROUP (ORDER BY col1 [desc|asc] [nulls first|last] [,col2 [desc|asc] [nulls first|last]]...) ：计算指定值在记录集中的排序值。
SELECT DENSE_RANK(14) WITHIN GROUP (ORDER BY empno) FROM emp ;