SQL高级查询.docx
《SQL高级查询.docx》由会员分享，可在线阅读，更多相关《SQL高级查询.docx（18页珍藏版）》请在冰点文库上搜索。
SQL高级查询
1.SQL（高级查询）
1.1.子查询
1.1.1.子查询在WHERE子句中
在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。
为了给查询提供数据而首先执行的查询语句叫做子查询。
子查询：
嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。
子查询嵌入的语句称作主查询或父查询。
主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。
根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。
多行多列或单行多列（没有什么实际意义）
多行多列子查询通常用于建立在二次查询,常出现在FROM子句中
图-1子查询
例如查找和SCOTT同职位的员工：
1.SELECTe.ename,e.job
2.FROMempe
3.WHEREe.job=
4.（SELECTjobFROMempWHEREename='SCOTT'）；
查找薪水比整个机构平均薪水高的员工：
1.SELECTdeptno,ename,sal
2.FROMempe
3.WHEREsal>（SELECTAVG（sal）FROMemp;
如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。
其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。
例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：
EXISTS:
后面跟着子查询
判断子查询返回是否存在,如果存在就输出,不存在就忽略这一条;
1.SELECTempno,ename,job,sal,deptno
2.FROMemp
3.WHEREdeptnoIN
4.（SELECTdeptnoFROMempWHEREjob='SALESMAN'）
5.ANDjob<>'SALESMAN';
在子查询中需要引用到主查询的字段数据，使用EXIST关键字。
EXIST后边的子查询至少返回一行数据，则整个条件返回TRUE。
如果子查询没有结果，则返回FALSE。
例如列出来那些有员工的部门信息：
1.SELECTdeptno,dnameFROMdeptd
2.WHEREEXISTS
3.（SELECT*FROMempe
4.WHEREd.deptno=e.deptno）;
1.1.2.子查询在HAVING部分
子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。
例如查询列出最低薪水高于部门30的最低薪水的部门信息：
1.SELECTdeptno,MIN（sal）min_sal
2.FROMemp
3.GROUPBYdeptno
4.HAVINGMIN（sal）>
5.（SELECTMIN（sal）FROMempWHEREdeptno=30）;
1.1.3.子查询在FROM部分
在查询语句中，FROM子句用来指定要查询的表。
如果要在一个子查询的结果中继续查询，则子查询出现在FROM子句中，这个子查询也称作行内视图或者匿名视图。
这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。
查询出薪水比本部门平均薪水高的员工信息：
1.SELECTe.deptno,e.ename,e.sal
2.FROMempe,
3.（SELECTdeptno,AVG（sal）avg_salFROMempGROUPBYdeptno）x
4.WHEREe.deptno=x.deptno
5.ande.sal>x.avg_sal
6.ORDERBYe.deptno;
1.1.4.子查询在SELECT部分
把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：
1.SELECTe.ename,e.sal,e.deptno,
2.（SELECTd.deptnoFROMdeptd
3.WHEREd.deptno=e.deptno）deptno
4.FROMempe;
1.2.分页查询
1.2.1.ROWNUM
ROWNUM被称作伪列，用于返回标识行数据顺序的数字。
例如：
1.SELECTROWNUM,empno,ename,sal
2.FROMemp;
ROWNUM只能从1计数，不能从结果集中直接截取。
下面的查询语句将没有结果：
1.SELECTROWNUM,empno,ename,sal
2.FROMemp
3.WHERErownum>3;
如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：
1.SELECT*FROM
2.（SELECTROWNUMrn,e.*FROMempe）
3.WHERErnBETWEEN8AND10;
也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件。
1.2.2.使用子查询进行分页
分页策略是指每次只取一页的数据。
当每次换页，取下一页的数据。
在ORACLE中利用ROWNUM的功能可用来进行分页。
假设结果集共105条，每20条分为一页，则共6页：
Page1：
1至20
Page2：
21至40
…
PageN:
（n-1）*pageSize+1至n*pageSize
1.2.3.分页与ORDERBY
按薪水倒序排列，取出结果集中第8到第10条的记录：
1.SELECT*FROM
2.（SELECTROWNUMrn,t.*FROM
3. （SELECTempno,ename,salFROMemp
4.ORDERBYsalDESC）t
5.）
6.WHERErnBETWEEN8AND10;
根据要查看的页数，计算起点值（（n-1）*pageSize+1）和终点值（n*pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。
--分页的三步骤
--排序
SELECT*FROMemporderbysaldesc;
--编号
selectrownumrn,e.*from（SELECT*FROMemporderbysaldesc）e;
--取范围注意取值范围在java中是按算法拼出来的
select*from（selectrownumrn,e.*from（SELECT*FROMemporderbysaldesc）e）
wherernbetween1and3
分页标准公式
Page-1
1.3.DECODE函数
1.3.1.DECODE函数基本语法
DECODE函数的语法如下：
DECODE（expr,search1,result1[,search2,result2…][,default]）
它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。
default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。
查询职员表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：
1.SELECTename,job,sal,
2.DECODE（job,'MANAGER',sal*1.2,
3.'ANALYST',sal*1.1,
4.'SALESMAN',sal*1.05,
5.sal
6.）bonus
7.FROMemp;
和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。
1.SELECTename,job,sal,
2.CASEjobWHEN'MANAGER'THENsal*1.2
3.WHEN'ANALYST'THENsal*1.1
4.WHEN'SALESMAN'THENsal*1.05
5.ELSEsalEND
6.bonus
7.FROMemp;
1.3.2.DECODE函数在分组查询中的应用
selectename,job,sal,decode（job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.5,
sal）bonusfromemp;
selectename,job,sal,casejobwhen'MANAGER'thensal*1.2
when'ANALYST'thensal*1.1
when'SALeSMAN'thensal*1.05
elsesalendbonusfromemp;
对于一个列中几个不同的列只看做一组,这时使用DECODE函数,现将不同的值转成相同的值,
DECODE函数可以按字段内容分组，例如：
计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUPBY简单实现。
用decode的实现方式：
1.SELECTDECODE（job,'ANALYST','VIP',
2.'MANAGER','VIP',
3.'OPERATION'）job,
4.COUNT
（1）job_cnt也可以写COUNT（job）
5.FROMemp
6.GROUPBYDECODE（job,'ANALYST','VIP','MANAGER','VIP','OPERATION'）;
图-2DECODE函数的运行结果
DECODE函数也可以按字段内容排序，例如：
Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序：
1.SELECTdeptno,dname,loc
2.FROMdept
3.ORDERBY
4.DECODE（dname,'研发部',1,'市场部',2,'销售部',3）,loc;
1.4.排序函数
1.4.1.ROW_NUMBER
ROW_NUMBER语法如下：
1.ROW_NUMBER（）OVER（
2.PARTITIONBYcol1ORDERBYcol2）
表示根据col1分组，在分组内部根据col2排序。
此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。
ROWNUM是伪列，ROW_NUMBER功能更强，可以直接从结果集中取出子集。
场景：
按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码
1.SELECTdeptno,ename,empno,
2.ROW_NUMBER（）
3.OVER（PARTITIONBYdeptnoORDERBYempno）ASemp_id
4.FROMemp;
5.
1.4.2.RANK
RANK函数的语法如下：
1.RANK（）OVER（
2.PARTITIONBYcol1ORDERBYcol2）
表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。
特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。
和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。
场景：
按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示
1.SELECTdeptno,ename,sal,comm,
2.RANK（）OVER（PARTITIONBYdeptno
3.ORDERBYsalDESC,comm）"Rank_ID"
4.FROMemp；
1.4.3.DENSE_RANK
DENSE_RANK函数的语法如下：
1.DENSE_RANK（）OVER（
2.PARTITIONBYcol1ORDERBYcol2）
表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。
特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。
场景：
关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水：
1.SELECTd.dname,e.ename,e.sal,
2.DENSE_RANK（）
3.OVER（PARTITIONBYe.deptnoORDERBYe.sal）
4.ASdrank
5.FROMempejoindeptd
6.one.deptno=d.deptno;
1.5.高级分组函数
1.5.1.ROLLUP
ROLLUP、CUBE和GROUPINGSETS运算符是GROUPBY子句的扩展，可以生成与使用UNIONALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。
语法形式如下：
∙GROUPBYROLLUP（a,b,c）
∙GROUPBYCUBE（a,b,c）
∙GROUPBYGROUPINGSETS（（a）,（b））
假设有表test，有a、b、c、d四个列。
1.SELECTa,b,c,SUM（d）FROMtestGROUPBYROLLUP（a,b,c）;
等价于：
1.SELECTa,b,c,SUM（d）FROMtestGROUPBYa,b,c
2.UNIONALL
3.SELECTa,b,null,SUM（d）FROMtestGROUPBYa,b
4.UNIONALL
5.SELECTa,null,null,SUM（d）FROMtestGROUPBYa
6.UNIONALL
7.SELECTnull,null,null,sum（d）FROMtest;
对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组（也就是全表分组）。
对于n个参数的ROLLUP，有n+1次分组。
表－1数据样例表
准备数据：
1.SQL>DROPTABLEsales_tab;
2.SQL>CREATETABLEsales_tab（
3.year_idNUMBERNOTNULL,
4.month_idNUMBERNOTNULL,
5.day_idNUMBERNOTNULL,
6.sales_valueNUMBER（10,2）NOTNULL
7.）;
8.SQL>INSERTINTOsales_tab
9.SELECTTRUNC（DBMS_RANDOM.value（low=>2010,high=>2012））ASyear_id,
10.TRUNC（DBMS_RANDOM.value（low=>1,high=>13））ASmonth_id,
11.TRUNC（DBMS_RANDOM.value（low=>1,high=>32））ASday_id,
12.ROUND（DBMS_RANDOM.value（low=>1,high=>100）,2）ASsales_value
13.FROMdual
14.CONNECTBYlevel<=1000;
15.SQL>COMMIT;
复习组函数的用法：
1.SQL>SELECTSUM（sales_value）ASsales_valueFROMsales_tab;
2.SQL>SELECTyear_id,COUNT（*）ASnum_rows,
3.SUM（sales_value）ASsales_value
4.FROMsales_tab
5.GROUPBYyear_id
6.ORDERBYyear_id;
7.SQL>SELECTyear_id,month_id,
8.COUNT（*）ASnum_rows,
9.SUM（sales_value）ASsales_value
10.FROMsales_tab
11.GROUPBYyear_id,month_id
12.ORDERBYyear_id,month_id;
图-3在测试表中使用组函数的运行结果
图-4在测试表中使用组函数的运行结果
ROLLUP函数的用法：
1.SELECTyear_id,month_id,
2.SUM（sales_value）ASsales_value
3.FROMsales_tab
4.GROUPBY
5.ROLLUP（year_id,month_id）
6.ORDERBYyear_id,month_id;
图-5在测试表中使用ROLLUP函数的运行结果
1.SELECTyear_id,month_id,day_id,SUM（sales_value）ASsales_value
2.FROMsales_tab
3.GROUPBYROLLUP（year_id,month_id,day_id）
4.ORDERBYyear_id,month_id,day_id;
图-6在测试表中使用ROLLUP函数的运行结果
ROLLUP（a,b,c）若根据a,b,c三列分组
则结果为:
先按照a,b,c分组一次,查询结果
再依次减去一个列,进行分组,
顺序为:
1:
abc
2:
ab
3:
a
4:
整张表为一组
分N+1次（N为参数数量）
1.5.2.CUBE
CUBE函数的语法形式：
1.GROUPBYCUBE（a,b,c）
对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。
对于n个参数的cube，有2^n次分组。
如果GROUPBYCUBE（a,b,c）,，首先对（a,b,c）进行GROUPBY，然后依次是（a,b），（a,c），（a），（b,c），（b），（c），最后对全表进行GROUPBY操作，所以一共是2^3=8次分组。
1.SELECTa,b,c,SUM（d）FROMtestGROUPBYCUBE（a,b,c）;
等价于：
1.SELECTa,b,c,SUM（d）FROMtestGROUPBYa,b,c
2.UNIONALL
3.SELECTa,b,NULL,SUM（d）FROMtestGROUPBYa,b
4.UNIONALL
5.SELECTa,NULL,c,SUM（d）FROMtestGROUPBYa,c
6.UNIONALL
7.SELECTa,NULL,NULL,SUM（d）FROMtestGROUPBYa
8.UNIONALL
9.SELECTNULL,b,c,SUM（d）FROMtestGROUPBYb,c
10.UNIONALL
11.SELECTNULL,b,NULL,SUM（d）FROMtestGROUPBYb
12.UNIONALL
13.SELECTNULL,NULL,c,SUM（d）FROMtestGROUPBYc
14.UNIONALL
15.SELECTNULL,NULL,NULL,SUM（d）FROMtest;
等价于只是方便理解，其内部运行机制并不相同，其效率远高于UNIONALL。
在sales_value表中使用cube函数：
1.SELECTyear_id,month_id,
2.SUM（sales_value）ASsales_value
3.FROMsales_tab
4.GROUPBYCUBE（year_id,month_id）
5.ORDERBYyear_id,month_id;
图-7在测试表中使用CUBE函数的运行结果
1.SELECTyear_id,month_id,day_id,
2.SUM（sales_value）ASsales_value
3.FROMsales_tab
4.GROUPBYCUBE（year_id,month_id,day_id）
5.ORDERBYyear_id,month_id,day_id;
1.5.3.GROUPINGSETS
GROUPINGSETS运算符可以生成与使用单个GROUPBY、ROLLUP或CUBE运算符所生成的结果集相同的结果集，但是使用更灵活。
如果不需要获得由完备的ROLLUP或CUBE运算符生成的全部分组，则可以使用GROUPINGSETS仅指定所需的分组。
GROUPINGSETS列表可以包含重复的分组。
GROUPINGSETS示例：
1.SELECTyear_id,month_id,SUM（sales_value）
2.FROMsales_tab
3.GROUPBYCUBE（year_id,month_id）
4.orderby1,2;
5.
6.SELECTyear_id,month_id,SUM（sales_value）
7.FROMsales_tab
8.GROUPBYGROUPINGSETS（（year_id）,（month_id））
9.orderby1,2
其中分组方式示例如下：
∙使用GROUPBYGROUPINGSETS（a,b,c），则对（a）,（b）,（c）进行GROUPBY
∙使用GROUPBYGROUPINGSETS（（a,b）,c）,则对（a,b）,（c）进行GROUPBY
∙GROUPINGBYGROUPINGSET（a,a）,则对（a）进行2次GROUPBY,GROUPINGSETS的参数允许重复
1.6.集合操作
1.6.1.UNION、UNION
升级会员 