sql

来源：互联网发布：增值税批量开票软件编辑：程序博客网时间：2024/06/05 11:48

并集：

--union 有去重操作,排序

select empno,ename,deptno fromemp

union

select empno,ename,deptno fromemp_tmp;

--union all 不去重

select empno,ename,deptno fromemp

union all

select empno,ename,deptno fromemp_temp

交集:intersect

select empno,ename,deptno fromemp

intersect

select empno,ename,deptno fromemp_tmp;

差集:minus

select empno,ename,deptno fromemp

minus

select empno,ename,deptno fromemp_tmp;

select empno,ename,deptno fromemp_tmp

minus

select empno,ename,deptno fromemp;

两个查询所枚举的列的数量不同:

select empno,ename,sal fromemp

union

select deptno,dname,null fromdept;

高级子查询

内外交互式子查询:

select ename,sal

from emp e

where sal>(select avg(sal)from empwhere deptno=e.deptno);

存在否:exists

select ename

from emp

where empno in (select distinct mgr from emp);

select ename

from emp e

where exists

(select 'x' from empwhere mgr=e.empno);

select ename

from emp e

where not exists

(select 'x' from empwhere mgr=e.empno);

in (set list): 枚举方式取数据,in后面跟随一个集合列表或指定的值！

select ename,sal fromempwhere dept in (10,20,30);

exists(exp true|false):检查子查询或指定行的存在性--〉常用于关联子查询中

exists的特性决定了只检查返回的行的存在性，因此查询不必返回一列。可以只

返回一个常量，来提高性能。

--error

select ename,sal fromempwheresalexists (10,20,30,null);

再例如：

select empno,sal fromemp outer where exists

( select 1 from empinner whereinner.empno=outer.empno);

以上查询使用了exists语句,exists()会执行outer.length次,它并不缓存exists()结果集,

因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回

true,没有则返回false.

非关联子查询对于exists和notexists是没有意义的;

非关联子查询使用notexists的话父查询总是返回0，使用exists总是返回父查询的查询结果集

分析函数

SQL> select rownum,t.ename,t.sal from

(select * from emporder by sal desc ) t;

select ename ,sal,row_number()over(order by sal desc) paiming fromemp ;

select ename ,sal,rank() over(order by sal desc) paiming fromemp ;

select ename,sal,rank()over(partition by deptno order by sal desc) as row_num,

deptno from emp ;

rank 返回一个唯一值，当碰到相同数据，此时所有相同数据的排名都是一样

的，同时会在后一条相同记录和下一条记录的排名中空出排名

dense_rank 返回一个唯一值，当碰到相同数据，此时所有相同数据的排名都

是一样的

row_number 返回一个唯一值，当碰到相同数据,排名按照记录顺序依次递增

行列转换

SELECT

MAX(DECODE(M1, 'HM-Site Map HazardousMaterials Business Plan', HMB))AS HMB22,

MAX(DECODE(M1,'HM-Certification StatementHazardous Materials Business Plan',

HMB)) AS HMB23

FROM (SELECT SERV_PROV_CODE,

B1_PER_ID1,

B1_PER_ID2,

B1_PER_ID3,

M1,

wm_concat(M3)AS HMB

FROM (SELECT SERV_PROV_CODE,

B1_PER_ID1,

B1_PER_ID2,

B1_PER_ID3,

ROW_INDEX,

MAX(DECODE(COLUMN_NAME,

'ReceivedDocument Type',

ATTRIBUTE_VALUE,

NULL)) AS M1,

MAX(DECODE(COLUMN_NAME,

'Status',

ATTRIBUTE_VALUE,

NULL)) AS M2,

MAX(DECODE(COLUMN_NAME, 'DateReviewed',ATTRIBUTE_VALUE,

NULL)) AS M3

FROM BAPPSPECTABLE_VALUE T0

WHERE (SERV_PROV_CODE ='COSD')

AND (TABLE_NAME = 'DOCSRECEIVED')

AND (B1_PER_ID1 = :CAPID1)

AND (B1_PER_ID2 = :CAPID2)

AND (B1_PER_ID3 = :CAPID3)

GROUP BY SERV_PROV_CODE,

B1_PER_ID1,

B1_PER_ID2,

B1_PER_ID3,

ROW_INDEX) A

WHERE (M2 = 'Accepted')

GROUP BY SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3, M1) B

GROUP BY SERV_PROV_CODE, B1_PER_ID1,B1_PER_ID2, B1_PER_ID3

几个常用的函数的使用

NVL、NVL2、NULLIF、DECODE、CASE

NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。

注意两者的类型要一致
NVL2(expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3

expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF(expr1, expr2) ->相等返回NULL，不等返回expr1

DECODE（expr1,expr2,’a’,expr3,’b’,…,’all’）->当expr2等于expr1,返回a;当expr3先于expr1时，返回b,…,else就返回all.

这里有一个优先顺序（从性能的角度上考量）：

NVL-》NVL2-》DECODE-》CASE

选择高效的辨识度高的索引

HINT:

我们能用得上的大多就这样/*+INDEX(TAB1 TAB_INDEX1)*/

0 0