1、SQL高级查询1. SQL(高级查询)1.1. 子查询1.1.1. 子查询在WHERE子句中在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。多行多列或单行多列(没有什么实际意义)多行多列子查询通常用于建立在二次查询,常出现在FROM
2、子句中图-1 子查询例如查找和SCOTT同职位的员工：1. SELECT e.ename, e.job2. FROM emp e3. WHERE e.job = 4. (SELECT job FROM emp WHERE ename = SCOTT)；查找薪水比整个机构平均薪水高的员工：1. SELECT deptno, ename, sal2. FROM emp e3. WHERE sal (SELECT AVG(sal) FROM emp;如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符、=、=一起使用。例如
3、查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：EXISTS:后面跟着子查询判断子查询返回是否存在,如果存在就输出,不存在就忽略这一条;1. SELECT empno, ename, job, sal, deptno2. FROM emp3. WHERE deptno IN 4. (SELECT deptno FROM emp WHERE job = SALESMAN)5. AND job SALESMAN;在子查询中需要引用到主查询的字段数据，使用EXIST关键字。EXIST后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。例如
4、列出来那些有员工的部门信息：1. SELECT deptno, dname FROM dept d2. WHERE EXISTS 3. (SELECT * FROM emp e4. WHERE d.deptno = e.deptno);1.1.2. 子查询在HAVING部分子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。例如查询列出最低薪水高于部门30的最低薪水的部门信息：1. SELECT deptno, MIN(sal) min_sal2. FROM emp3. GROUP BY deptno4. HAVING MIN(sal) 5. (SELECT MIN(sal)
5、FROM emp WHERE deptno = 30);1.1.3. 子查询在FROM部分在查询语句中，FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。查询出薪水比本部门平均薪水高的员工信息：1. SELECT e.deptno, e.ename, e.sal2. FROM emp e,3. (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x4. WHERE e.
6、deptno = x.deptno5. and e.sal x.avg_sal6. ORDER BY e.deptno;1.1.4. 子查询在SELECT部分把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：1. SELECT e.ename, e.sal, e.deptno,2. (SELECT d.deptno FROM dept d 3. WHERE d.deptno = e.deptno) deptno4. FROM emp e;1.2. 分页查询1.2.1. ROWNUMROWNUM被称作伪列，用于返回标识行数据顺序的数字。例如：1. SELECT RO
7、WNUM, empno, ename, sal 2. FROM emp;ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：1. SELECT ROWNUM, empno, ename, sal2. FROM emp3. WHERE rownum 3;如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：1. SELECT * FROM 2. (SELECT ROWNUM rn , e.* FROM emp e ) 3. WHERE rn BETWEEN 8 AND 10;也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件。1.2.
8、2. 使用子查询进行分页分页策略是指每次只取一页的数据。当每次换页，取下一页的数据。在ORACLE中利用ROWNUM的功能可用来进行分页。假设结果集共105条，每20条分为一页，则共6页：Page1： 1 至 20Page2： 21 至40PageN: (n - 1) * pageSize + 1 至 n * pageSize1.2.3. 分页与ORDER BY按薪水倒序排列，取出结果集中第8到第10条的记录：1. SELECT * FROM 2. (SELECT ROWNUM rn , t.* FROM 3. (SELECT empno,ename,sal FROM emp 4. ORDER
9、 BY sal DESC) t5. ) 6. WHERE rn BETWEEN 8 AND 10;根据要查看的页数，计算起点值（(n - 1) * pageSize + 1）和终点值（n * pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。-分页的三步骤-排序SELECT * FROM emp order by sal desc;-编号select rownum rn,e.* from (SELECT * FROM emp order by sal desc) e;-取范围 注意取值范围在java中是按算法拼出来的select * from(select rownu
10、m rn,e.* from (SELECT * FROM emp order by sal desc) e)where rn between 1 and 3分页标准公式Page-11.3. DECODE函数1.3.1. DECODE函数基本语法DECODE函数的语法如下：DECODE (expr, search1, result1, search2, result2, default)它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。
11、default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。查询职员表，根据职员的职位计算奖励金额，当职位分别是MANAGER、ANALYST、SALESMAN时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：1. SELECT ename, job, sal,2. DECODE(job, MANAGER, sal * 1.2,3. ANALYST, sal * 1.1,4. SALESMAN, sal * 1.05,5. sal6. ) bonus7. FROM emp;和DECODE函数功能相似的有CASE语句
12、，实现类似于if-else的操作。1. SELECT ename, job, sal,2. CASE job WHEN MANAGER THEN sal * 1.23. WHEN ANALYST THEN sal * 1.14. WHEN SALESMAN THEN sal * 1.055. ELSE sal END6. bonus7. FROM emp;1.3.2. DECODE函数在分组查询中的应用select ename,job,sal,decode(job, MANAGER,sal*1.2, ANALYST,sal*1.1, SALESMAN,sal*1.5, sal)bonus fr
13、om emp; select ename,job,sal,case job when MANAGERthen sal*1.2when ANALYSTthen sal*1.1when SALeSMANthen sal*1.05else sal end bonus from emp;对于一个列中几个不同的列只看做一组,这时使用DECODE函数,现将不同的值转成相同的值,DECODE函数可以按字段内容分组，例如：计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUP BY简单实现。用decode的实现方式：1. SELECT DECO
14、DE(job, ANALYST, VIP, 2. MANAGER, VIP, 3. OPERATION) job,4. COUNT(1) job_cnt 也可以写 COUNT(job)5. FROM emp6. GROUP BY DECODE(job, ANALYST, VIP, MANAGER, VIP, OPERATION);图-2 DECODE函数的运行结果DECODE函数也可以按字段内容排序，例如：Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序：1. SELECT deptno, dname, loc2. FROM dept3. O
15、RDER BY 4. DECODE(dname, 研发部,1,市场部,2,销售部,3), loc;1.4. 排序函数1.4.1. ROW_NUMBERROW_NUMBER语法如下：1. ROW_NUMBER() OVER(2. PARTITION BY col1 ORDER BY col2)表示根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集。场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码1. SELECT deptno, ename, e
16、mpno,2. ROW_NUMBER()3. OVER (PARTITION BY deptno ORDER BY empno) AS emp_id4. FROM emp;5. 1.4.2. RANKRANK函数的语法如下：1. RANK() OVER(2. PARTITION BY col1 ORDER BY col2)表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。场景：按照部
17、门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示1. SELECT deptno, ename, sal, comm,2. RANK() OVER (PARTITION BY deptno3. ORDER BY sal DESC, comm) Rank_ID4. FROM emp；1.4.3. DENSE_RANKDENSE_RANK函数的语法如下：1. DENSE_RANK() OVER(2. PARTITION BY col1 ORDER BY col2)表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排
18、名。特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水：1. SELECT d.dname, e.ename, e.sal, 2. DENSE_RANK() 3. OVER (PARTITION BY e.deptno ORDER BY e.sal) 4. AS drank5. FROM emp e join dept d6. on e.deptno = d.deptno;1.5. 高级分组函数1.5.1. ROLLUPROLLUP、CUBE 和
19、GROUPING SETS 运算符是 GROUP BY 子句的扩展，可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。语法形式如下： GROUP BY ROLLUP(a, b, c) GROUP BY CUBE(a, b, c) GROUP BY GROUPING SETS ( (a), (b)假设有表test，有a、b、c、d四个列。1. SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);等价于：1. SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,
20、c2. UNION ALL3. SELECT a,b,null,SUM(d) FROM test GROUP BY a,b4. UNION ALL5. SELECT a,null,null,SUM(d) FROM test GROUP BY a6. UNION ALL7. SELECT null,null,null,sum(d) FROM test;对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。表1 数据样例表准备数据：1. SQLDROP TABLE sales_tab;2. SQLCREATE T
21、ABLE sales_tab (3. year_id NUMBER NOT NULL,4. month_id NUMBER NOT NULL,5. day_id NUMBER NOT NULL,6. sales_value NUMBER(10,2) NOT NULL7. );8. SQLINSERT INTO sales_tab9. SELECT TRUNC(DBMS_RANDOM.value(low = 2010, high = 2012) AS year_id,10. TRUNC(DBMS_RANDOM.value(low = 1, high = 13) AS month_id,11. T
22、RUNC(DBMS_RANDOM.value(low = 1, high = 32) AS day_id,12. ROUND(DBMS_RANDOM.value(low = 1, high = 100), 2) AS sales_value13. FROM dual14. CONNECT BY level COMMIT;复习组函数的用法：1. SQLSELECT SUM(sales_value) AS sales_value FROM sales_tab;2. SQLSELECT year_id, COUNT(*) AS num_rows,3. SUM(sales_value) AS sale
23、s_value 4. FROM sales_tab 5. GROUP BY year_id 6. ORDER BY year_id;7. SQLSELECT year_id, month_id,8. COUNT(*) AS num_rows,9. SUM(sales_value) AS sales_value 10. FROM sales_tab 11. GROUP BY year_id, month_id 12. ORDER BY year_id, month_id; 图-3 在测试表中使用组函数的运行结果图-4 在测试表中使用组函数的运行结果ROLLUP函数的用法：1. SELECT ye
24、ar_id, month_id, 2. SUM(sales_value) AS sales_value3. FROM sales_tab4. GROUP BY 5. ROLLUP (year_id, month_id)6. ORDER BY year_id, month_id;图-5 在测试表中使用ROLLUP函数的运行结果1. SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value2. FROM sales_tab3. GROUP BY ROLLUP (year_id, month_id, day_id)4. ORD
25、ER BY year_id, month_id, day_id;图-6 在测试表中使用ROLLUP函数的运行结果ROLLUP(a,b,c)若根据a,b,c三列分组则结果为:先按照a,b,c分组一次,查询结果再依次减去一个列,进行分组,顺序为:1:abc2:ab3:a4:整张表为一组分N+1次(N为参数数量)1.5.2. CUBECUBE函数的语法形式：1. GROUP BY CUBE(a, b, c)对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。对于n个参数的cube，有2n次分组。如果GROUP BY CUBE(a,b,
26、c),，首先对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，所以一共是23=8次分组。1. SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);等价于：1. SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c2. UNION ALL3. SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b4. UNION ALL5. SELECT a,NULL,c,SUM(d) FROM t
27、est GROUP BY a,c6. UNION ALL7. SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a8. UNION ALL9. SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c10. UNION ALL11. SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b12. UNION ALL13. SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c14. UNION ALL15. SELECT NULL,NULL,NUL
28、L,SUM(d) FROM test ;等价于只是方便理解，其内部运行机制并不相同，其效率远高于UNION ALL。在sales_value表中使用cube函数：1. SELECT year_id, month_id,2. SUM(sales_value) AS sales_value3. FROM sales_tab4. GROUP BY CUBE (year_id, month_id)5. ORDER BY year_id, month_id;图-7 在测试表中使用CUBE函数的运行结果1. SELECT year_id, month_id, day_id,2. SUM(sales_val
29、ue) AS sales_value3. FROM sales_tab4. GROUP BY CUBE (year_id, month_id, day_id)5. ORDER BY year_id, month_id, day_id;1.5.3. GROUPING SETSGROUPING SETS 运算符可以生成与使用单个 GROUP BY、ROLLUP 或 CUBE 运算符所生成的结果集相同的结果集，但是使用更灵活。如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复
30、的分组。GROUPING SETS示例：1. SELECT year_id, month_id, SUM(sales_value) 2. FROM sales_tab 3. GROUP BY CUBE (year_id,month_id)4. order by 1, 2; 5. 6. SELECT year_id, month_id, SUM(sales_value) 7. FROM sales_tab 8. GROUP BY GROUPING SETS ( (year_id), (month_id)9. order by 1, 2其中分组方式示例如下： 使用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, GROUPING SETS的参数允许重复1.6. 集合操作1.6.1. UNION、UNION
