oracle 高级SQL查询笔记12-01

<!-- [if !mso]> <style> v/:* {behavior:url(#default#VML);} o/:* {behavior:url(#default#VML);} w/:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style> <![endif]--><!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

0 ：coalesce 函数的用法？

COALESCE(A,B,C,D) AS 别名
返回参数表中第一个不为空的值( 从左开始)
A B C D 可以是字段, 也可以是其他函数的返回值或者表达式的结果值

1 ：group by rollup 的用法。

Oracle 数据库中的rollup 配合group by 命令使用，可以提供信息汇总功能（与" 小计" 相似）

示例如下：

select decode (job, '' , ' 合计 ' , job) job,

decode (empno, '' , ' 小计 ' , 'empno' ) empno,

sum (sal)

from emp

group by rollup (job, empno)

可以看出：

用了 rollup 的 group by 子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。

例如在 scott 用户下面有一个 emp 表：
select deptno,job,sum(sal) from emp group by deptno,job;

会对每一个不同的 dept,job 生成一行独立的结果。

而
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
的结果中除了上述的结果结果之外，还会对每一个 deptno 进行一个小结，并单独生成一行，除此之外还会对所有的 sal 求和并生成一列。

2 ： having 字句的用法详解？

select job, sum (sal) from emp group by job

having sum (sal)> 5000

在这里，我们不能用 where 来筛选超过 5000 的薪水，因为表中不存在这样一条记录。

相反， HAVING 子句可以让我们筛选成组后的各组数据 。

3 ：

rollup 从右往左再聚合 用法和 1 一样。

cube 从左往右取

1 ：： grouping sets 用法？？？？？？？

分别求统计，再 union

分别求统计，再 union

select id , num , sum (age) from users group by grouping sets (( id ),( num ))

select id , num , sum (age) from users group by rollup (( id , num ));

SELECT A, B, C, 

SUM( D )

FROM t

GROUP BY GROUPING SETS 

( A, B, C )

等价于：

SELECT * FROM (

 ( SELECT A, NULL, NULL, SUM( D )

FROM t GROUP BY A )

 
UNION ALL

 ( SELECT NULL, B, NULL, SUM( D )

FROM t GROUP BY B )

 
UNION ALL

 ( SELECT NULL, NULL, C, SUM( D )

FROM t GROUP BY C )

)

成对的进行比较

select student_id,teacher_id,class_id from students

where (teacher_id,class_id) in ( select teacher_id,class_id from students where student_id in

( 222 , 332 ))

在 from 中使用子查询 , 注意要加上别名

select a.empno,a.ename from emp a,( select b.empno,b.sal from emp b) c where

c.empno=a.empno

1 ： exists , not exists 和 in 的效率问题？

select t1.job from emp t1 where exists ( select t2.job from emp t2 where t2.job like '%T%' );

-- 当 T1 的数据量小而 T2 的数据量大的时候使用，查询的效率比较高。

select T1.job from emp T1 where T1.JOB IN ( select T2.job from emp T2 where T2.job like '%T%' );

-- 当 T1 的数据量非常大二 T2 的数据量小的时候 ，查询的效率比较高。

Not in 和not exit 的效率问题？

数据库：oracle

数据量：52000 条记录

用not exists 比not in 执行的效率要高的多

select * from quyu_t a where a.version='256' and not exists(select 'x' from quyu_t_his b
where a.username=b.username);

------- 用not in 效率低

select * from quyu_t where username not in (select username from quyu_t_his)

2 个一比较简直是太明显了

10 ：oracle rank() 函数总结

首先 , 要了解 rank 在英语的意思 : 等级 . 也就是说这是一个给数据确定等级的函数 .

rank() 对表中的数据进行分级排序 .

例如：

select e.ename,e.job,e.mgr,e.sal,

rank ( ) over ( PARTITION by e.job order by e.sal desc ) rank1

from emp e

执行结果图如下：

意思就是先按照 job 进行分级。 对 job 的每一个级别的数据进行排序。可以出现同一等级。

比如 analyst 都是并立第一则 rank1 都为 1 ， 在每一个级别里边在按照 order by 进行排序。比如 clerk rank1 分别就是 1,2,3,4.

如果要去每一个分组里的第一名的话则如下得到 SQL ：

select * from (

select e.ename,e.job,e.mgr,e.sal,

rank ( ) over ( PARTITION by e.job order by e.sal desc ) rank1

from emp e

) where rank1= '1'

效果如下：

这样 rank1 就取出每一个里边是第一个名的数据。重复的也存在。