oracle 高级SQL查询笔记12-01

abwjnspcya

　　<!-- [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
就取出每一个里边是第一个名的数据。重复的也存在。