ORACLE学习（六）

--集合

交集 INTERSECT 两个集合共有的

并集 UNION(去重)/UNION ALL（不去重） 两个集合的相加

差集 MINUS

A={1,2,3,4,5,6}

B={1,3,4,6,9}

C=1,3,7,9

A INTERSECT B ->{1,3,4,6}

A UNION B->{1,2,3,4,5,6,9}

A UNION ALL B->{1,2,3,4,5,6,1,3,4,6,9}

A MINUS B ->{2,5} --A和B比较A独有的数据

B MINUS A ->{9} --B和A比较B独有的数据

--行列转换

行转列(行中值变成字段) case when/decode +聚合函数（变化的项）+分组（未变化的项,与 SELECT 后面一致）

列转行 union all

--把kecheng中的course列进行行转列

SELECT ID,NAME,

SUM(CASE COURSE WHEN '语文' THEN SCORE END) 语文,

SUM(CASE COURSE WHEN '数学' THEN SCORE END) 数学,

SUM(CASE COURSE WHEN '英语' THEN SCORE END) 英语,

SUM(CASE COURSE WHEN '历史' THEN SCORE END) 历史,

SUM(CASE COURSE WHEN '化学' THEN SCORE END) 化学

FROM KECHENG

GROUP BY ID,NAME

ORDER BY ID;

--把课程中的name列进行行转列

SELECT COURSE,

SUM(CASE NAME WHEN '张三' THEN SCORE END) 张三,

SUM(CASE NAME WHEN '李四' THEN SCORE END) 李四,

SUM(CASE NAME WHEN '王五' THEN SCORE END) 王五

FROM KECHENG

GROUP BY COURSE;

--把demo中的name列行转列

SELECT ID,

SUM(DECODE(NAME,'苹果',NUMS,0)) 苹果,

SUM(DECODE(NAME,'橘子',NUMS,0)) 橘子,

SUM(DECODE(NAME,'葡萄',NUMS,0)) 葡萄,

SUM(DECODE(NAME,'芒果',NUMS,0)) 芒果

FROM DEMO

GROUP BY ID

ORDER BY ID;

--把demo中的id列行专列

SELECT NAME,

SUM(DECODE(ID,1,NUMS,0)) 一季度,

SUM(DECODE(ID,2,NUMS,0)) 二季度,

SUM(DECODE(ID,3,NUMS,0)) 三季度,

SUM(DECODE(ID,4,NUMS,0)) 四季度

FROM DEMO

GROUP BY NAME

--列转行

--把demo1进行列转行

SELECT * FROM (

SELECT ID,'苹果' NAME,苹果 NUMS FROM DEMO1

union all

SELECT ID,'橘子' NAME,橘子 NUMS FROM DEMO1

union all

SELECT ID,'葡萄' NAME,葡萄 NUMS FROM DEMO1

union all

SELECT ID,'芒果' NAME,芒果 NUMS FROM DEMO1

) WHERE NUMS IS NOT NULL;

--把demo2进行列转行

SELECT * FROM (

select 1 id,NAME,一季度 NUMS FROM DEMO2

UNION ALL

select 2 id,NAME,二季度 NUMS FROM DEMO2

UNION ALL

select 3 id,NAME,三季度 NUMS FROM DEMO2

UNION ALL

select 4 id,NAME,四季度 NUMS FROM DEMO2

) WHERE NUMS IS NOT NULL;

--行列转换的函数

行转列(列中值不能重复)

PIVOT(聚合函数(被聚合的列) FOR 行转列的列 IN(列中值。。。))

列转行

UNPIVOT(被聚合的列的新列名 FOR 列转行的新列名 IN(字段名。。。。))

--把demo的name列进行行转列

select * FROM DEMO

PIVOT(SUM(NUMS) FOR NAME IN('苹果' 苹果,'橘子' 橘子,'葡萄','芒果'));

--把kecheng中的name进行行转列

SELECT * FROM (SELECT NAME,COURSE,SCORE FROM KECHENG)

PIVOT(SUM(SCORE) FOR NAME IN('张三' 张三,'李四' 李四,'王五' 王五));

--把demo1进行列转行

SELECT * FROM DEMO1

UNPIVOT(NUMS FOR NAME IN(苹果,橘子,葡萄,芒果));

--把demo2进行列转行

SELECT ID,NAME,NUMS FROM(

SELECT * FROM (SELECT NAME,一季度 AS "1",二季度 AS "2", 三季度 AS "3", 四季度 AS "4"

FROM DEMO2)

UNPIVOT(NUMS FOR ID IN("1","2","3","4")));

SELECT DECODE(ID,'一季度',1,'二季度',2,'三季度',3,4) id,NAME,NUMS FROM(

SELECT * FROM DEMO2 UNPIVOT(NUMS FOR ID IN(一季度 ,二季度 ,三季度 ,四季度 )));

--树形查询（层级查询）

树形查询通常由根节点 父节点 子节点 叶子节点构成

根节点：当前节点之上没有节点的节点

父节点：当前节点之下有节点的节点

子节点：当前节点之上有节点的节点

叶子节点：当前节点之下没有节点的节点

level 代表节点的深度

树形查询的语法：

SELECT 列。。。[LEVEL]

FROM 表

[WHERE 条件]

[START WITH 条件] --开始的条件 从谁开始查 不写默认每个点开始

CONNECT BY PRIOR 儿子列=父亲列 --查询下级

父亲列=儿子列 --查询上级

--查询emp的层次结构

SELECT LEVEL,LPAD('',5LEVEL,'*')||ENAME FROM EMP CONNECT BY PRIOR EMPNO=MGR;

--查询KING的下属关系

SELECT LEVEL,LPAD('',5LEVEL,'*')||ENAME FROM EMP

START WITH ENAME='KING'

CONNECT BY PRIOR EMPNO=MGR;

--查询ADAMS的上级

SELECT ENAME FROM EMP

START WITH ENAME='ADAMS'

CONNECT BY PRIOR MGR=EMPNO;

--查询KING的下属的下属

SELECT ENAME FROM EMP

WHERE LEVEL=3

START WITH ENAME='KING'

CONNECT BY PRIOR EMPNO=MGR;

--查询SCOTT的直属领导

SELECT ENAME FROM EMP

WHERE LEVEL=2

START WITH ENAME='SCOTT'

CONNECT BY PRIOR MGR=EMPNO;

--伪列

可以向其他列一样被查询 但是不能修改和删除

LEVEL

ROWNUM

ROWID

LEVEL:必须从1开始 而且是连续的 必须和connect by配合使用

用法：1、树形查询返回节点深度

2、生成多条记录

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=50;

--查询今年所有的星期五

SELECT LV FROM (SELECT TRUNC(SYSDATE,'YYYY')-1+LEVEL LV

FROM DUAL

CONNECT BY LEVEL<=TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-1,'DDD')

) WHERE TO_CHAR(LV,'D')=6;

--查询2008年到2021年 所有的月初 月末

SELECT ADD_MONTHS(DATE'2007-12-1',LEVEL) 月初,

LAST_DAY(ADD_MONTHS(DATE'2007-12-1',LEVEL)) 月末

FROM DUAL

CONNECT BY LEVEL<=12*(2021-2008+1)

--ROWNUM

必须从1开始而且是连续的

--查询emp的前五航

SELECT * FROM EMP WHERE ROWNUM<=5;

--查询最早的入职日期的员工信息

SELECT * FROM (SELECT * FROM EMP ORDER BY HIREDATE) WHERE ROWNUM<2;

--查询emp的6~10行

SELECT * FROM (select E.*,ROWNUM RN FROM EMP E)

WHERE RN BETWEEN 6 AND 10;

--查询emp中工资最高的5个人的员工信息

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC )WHERE ROWNUM<=5;

--rownum主要使用在分页查询上

--以emp为列 分页查询 每页显示5条数据

1 1~5

2 6~10

3 11~15

n 5N-4~5N

SELECT * FROM (SELECT E.,ROWNUM RNFROM EMP E)WHERE RN BETWEEN 5&页码-4 and 5*&页码;

--按照工资降序 分页查询emp的数据 每页显示3条数据

SELECT * FROM (SELECT E.,ROWNUM RN FROM (select * FROM EMP ORDER BY SAL DESC) E)WHERE RN BETWEEN 3&页-2 AND 3*&页;