高级查询、排序函数、集合操作

高级查询

DECODE函数基本语法
1、DECODE函数的语法如下：
2、它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的
result结果。
3、可以有多组search和result的对应关系，如果任何一个search条件都没有匹配
到，则返回最后default的值。
4、default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返
回NULL。

DECODE (expr,
 search1, result1
[, search2, result2…]
[, default] ) 

DECODE函数在分组查询中的应用：
1、DECODE函数可以按字段内容分组。
2、DECODE函数也可以按字段内容排序。

DECODE函数可以按字段内容分组

‐‐计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUP BY简单实现。用decode的实现方式： 
	代码案例：
	select DISTINCT job,count(*)
	from (Select 
	decode(job,			--expr
	    'ANALYST','VIP',		--search1，result1
	    'MANAGER','VIP',		--search2，result2
	    'OPERATION'		--default
	    ) AS JOB
	    FROM EMP_ZLD)
	    group by job;

DECODE函数也可以按字段内容排序

‐‐Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序： 
	 SELECT deptno, dname, loc 
	 FROM dept 
	 ORDER BY 
	 DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc; 

‐‐和DECODE函数功能相似的有CASE语句，实现类似于if‐else的操作。

--结束时，用end结束
 SELECT ename, job, sal, 
 CASE  job WHEN 'MANAGER' THEN sal * 1.2 
 WHEN 'ANALYST' THEN sal * 1.1 
 WHEN 'SALESMAN' THEN sal * 1.05 
 ELSE sal END 
 bonus FROM emp; 

排序函数

ROW_NUMBER

1、ROW_NUMBER语法如下：
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
2、表示根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。

–代码案例：
–按照部门编码分组显示，每组内按职员编码排序，

select ename,empno,deptno,
    ROW_NUMBER()
        over(PARTITION BY deptno order by empno)
        as deptno_id
        from emp_zld;

RANK

1、RANK函数的语法如下：
RANK() OVER(PARTITION BY col1 ORDER BY col2)
2、表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据
返回相同排名。
3、特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数
据都标记为2，但下一位将是第四名。
4、和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。

代码案例：

--学员表的class_id数据参照class表cid列的数据，关联学员表student和class表，按照class_id分组，
--每组内按照学员成绩排序，相同成绩按照学号正序排序，列出学生所在的专业名字、学生姓名、成绩及等级drank。
select cname,sname,score,
    RANK()
    OVER(PARTITION BY CLASS_ID ORDER BY SCORE DESC,SID)
    AS DRANK
    FROM STUDENT_ZZZ JOIN
    CLASS_ZLD
    ON student_zzz.class_id = CLASS_ZLD.CID;

集合操作

UNION、UNION ALL

1、为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、 交、差。
2、集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。
3、ORDER BY子句只能放在最后的一个查询语句中。

集合操作的语法如下：

SELECT statement1 
[UNION | UNION ALL | INTERSECT | MINUS] 
SELECT statement2; 

UNION和UNION ALL

UNION和UNION ALL用来获取两个或两个以上结果集的并集：
UNION操作符会自动去掉合并后的重复记录。
UNION ALL返回两个结果集中的所有行，包括重复的行。
UNION操作符对查询结果排序，UNION ALL不排序。

INTERSECT

1、INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数
据，才被显示输出。
2、使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

MINUS

1、MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个
结果集中不存在的数据，才能够被显示出来。
2、也就是结果集一减去结果集二的结果。

高级分组函数：

ROLLUP
CUBE
GROUPING SETS

语法如下：
GROUP BY ROLLUP(a, b, c)
对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。
对于n个参数的ROLLUP，有n+1次分组。
–GROUP BY(A,B,C) UNION ALL GROUP BY(A,B) UNION ALL BY(A) UNION ALL EMP

GROUP BY CUBE(a, b, c)
对于n个参数的cube，有2^n次分组。
3如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，然后依次是
(a,b)，(a,c)，(a)，(b,c)，(b)， ( c)，最后对全表进行GROUP BY操作，所以一共
是2^3=8次分组。

GROUP BY GROUPING SETS ( (a), (b))
使用GROUP BY GROUPING SETS(a,b,c)，则对(a),(b),( c )进行GROUP BY使用GROUP BY GROUPING SETS((a,b),c), 则对(a,b),( c )进行GROUP BY
GROUPING BY GROUPING SET(a,a) , 则对(a)进行2次GROUP BY