7.oracle集合运算与行列转换

--并集，交集，差集
1.并集去重 union 去重以第一个字段排序显示
2.并集不去重 union all 将两个字段合并 不排序;
--列名以第一个表字段显示,两个表对应字段数据类型相同，字段可以不同
SELECT DEPTNO AS 编号,dname as 名称 from dept
union
select empno,ename from emp;

select deptno from dept
union 
select deptno from dept;

select deptno from dept
union all
select deptno from dept;

3. intersect-交集 将两个查询结果共有的数据去重，排序;
select job ROM EMP WHERE DEPTNO=10
INTERSECT
SELECT JOB FROM EMP WHERE DEPTNO=20;

4.差集-minus 取仅在第一个表中存在的数据，去重，排序;
select DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;
SELECT DEPTNO FROM EMP
MINUS
select DEPTNO FROM DEPT;
注意点：除了 union all 其他的都去重，别名写在第一个查询，排序写在最后一个查询,排序写在第一个报错;
        综合集合运算从上到下一次执行;



----行列转换
建立行列转换随需表
--第一个表--------
三个字段;--姓名-科目-成绩
CREATE TABLE SCORE_1(STUDENT VARCHAR2(12),COURSE VARCHAR2(7),SCORE NUMBER);
SELECT * FROM SCORE_1;
INSERT INTO SCORE_1 VALUES('张三','CHINESE',70);
INSERT INTO SCORE_1 VALUES('张三','ENGLISH',90);
INSERT INTO SCORE_1 VALUES('张三','MATH',80);
INSERT INTO SCORE_1 VALUES('李四','CHINESE',76);
INSERT INTO SCORE_1 VALUES('李四','ENGLISH',91);
INSERT INTO SCORE_1 VALUES('李四','MATH',82);
INSERT INTO SCORE_1 VALUES('王五','CHINESE',85);
INSERT INTO SCORE_1 VALUES('王五','ENGLISH',70);
INSERT INTO SCORE_1 VALUES('王五','MATH',90);
COMMIT;
--建立第二个表 四个字段--姓名-CHINESE成绩--MATH成绩--ENGLISH成绩
CREATE TABLE SCORE_2(STUDENT VARCHAR2(12),CHINESE NUMBER,MATH NUMBER,ENGLISH NUMBER);
INSERT INTO SCORE_2 VALUES('张三',70,80,90);
INSERT INTO SCORE_2 VALUES('李四',76,82,91);
INSERT INTO SCORE_2 VALUES('王五',85,90,70);
COMMIT;
SELECT * FROM SCORE_2;  --插入数据之后查看一下表
SELECT * FROM SCORE_1;
---行列转换实现方法
1.传统方法
将某一维度的内容拆解成多个新的列：聚合函数+CASE WHEN语句
通过聚合函数,case when 分组函数实现 语法如下：
sum() case when... group by 再搭配集合运算实现

select t.*,case when COURSE='CHINESE' then score else 0 end CHINESE,        
           case when COURSE='ENGLISH' then score else 0 end CHINESE,
           case when COURSE='MATH' then score else 0 end CHINESE
 from SCORE_1 t;

/*select student,sum(score) score,sum(chinese) chinese,sum( math) math from (
select t.*,case when COURSE='CHINESE' then score else 0 end CHINESE,        
           case when COURSE='ENGLISH' then score else 0 end english,
           case when COURSE='MATH' then score else 0 end math
 from SCORE_1 t
) group by student;*/

SELECT STUDENT,SUM(CASE WHEN COURSE='CHINESE' THEN SCORE ELSE 0 END) CHINESE,
               SUM(CASE WHEN COURSE='MATH' THEN SCORE ELSE 0 END) MATH,
               SUM(CASE WHEN COURSE='ENGLISH' THEN SCORE ELSE 0 END) ENGLISH
               FROM SCORE_1 GROUP BY STUDENT;
               
聚合函数+decode函数
DECODE语法;
DECODE(EXPR,VALUE1,RESULT1,VALUE2,RESULT2,…,DEF_RESULT);
select student,sum(decode(course,'CHINESE',SCORE,0)) CHINESE,
               sum(decode(course,'MATH',SCORE,0)) MATH,
               sum(decode(course,'ENGLISH',SCORE,0)) ENGLiSH
               FROM SCORE_1 GROUP BY STUDENT;
               
列转行（将SCORE_2转换成SCORE_1）;
将多个列合并成为一个新的维度：单查多列+并集运算（UNION ALL）;
select student,'chinese' as course,chinese score from score_2
union all 
select student,'math' as course,math score from score_2
union all
select student,'english' as course,english score from score_2;
/*之所以使用UNION ALL而不用UNION的原因是因为UNION ALL不会将重复的数据去掉，确保了数据的完整性，
而UNION会先去重再合并，可能会导致合并的数据不完整。*/

2.专用函数（pivot/unpivot）（都写在FROM后面）
2.1行转列 （将SCORE_1转为SCORE_2）--PIVOT 将维度数据拆解成多个列 --对应  SUM(CASE WHEN ...)和 SUM(DECODE())
语法：


SELECT * FROM TB PIVOT(SUM(TB.COL1) FOR TB.COL2 IN (VAL1 AS NEW_COL1,VAL2 AS NEW_COL2,VAL3 AS NEW_COL3));
PIVOT函数注释：
PIVOT函数内一定要有聚合函数 MAX()
TB.COL1:被聚合分析的字段  SCORE
TB.COL2:被拆解的字段  COURSE
VAL1,VAL2,VAL3...:TB.COL2字段中的某值
NEW_COL1,NEW_COL2,NEW_COL3...:TB.COL2字段拆解后形成的新字段
AS NEW_COL部分可以不写，最终会以VAL作为字段名展示
SELECT *
  FROM 表名
PIVOT(MAX(列名)   [要转成的字段下的数据]
FOR 要转的列 IN('要转的列下的字符名' AS 转成的列名,
               '要转的列下的字符名' AS 转成的列名,
               '要转的列下的字符名' AS 转成的列名,
               '要转的列下的字符名' AS 转成的列名))
WHERE 1 = 1; --没有条件可以不写
select * from score_1 pivot(max(score) for course in ('CHINESE' as chinese,
                                                  'ENGLISH' as english,
                                                  'MATH' as math));                                            
select * from score_1 pivot(max(score) for STUDENT in ('王五' as wangwu,
                                                  '李四' as lisi,
                                                  '张三' as zhangsan));                                                 
2.2列转行（将SCORE_2转为SCORE_1）--UNPIVOT 将多个列合并成一个新的维度 --对应 UNION ALL
--语法：
SELECT * FROM TB UNPIVOT(NEW_COL1 FOR NEW_COL2 IN (TB.COL1 AS VAL1,TB.COL2 AS VAL2,TB.COL3 AS VAL3));
/*UNPIVOT函数注释：
UNPIVOT函数内不需要聚合函数
NEW_COL1:用来收纳交叉数据的字段，字段名自取 SCORE
NEW_COL2:新合成的维度字段，字段名自取   COURSE

TB.COL1,TB.COL2,TB.COL3...:被并入的字段
VAL1,VAL2,VAL3...:并入字段以怎样的数据内容录入新维度字段
AS VAL部分可以不写，最终会以TB.COL作为数据内容录入 */       
--代码：
SELECT * FROM SCORE_2 UNPIVOT(COURSE FOR SCORE IN (CHINESE,MATH,ENGLISH));

注意：有的数据库并没有PIVOT和UNPIVOT这两个专用函数，所以我们一般做行列转换用的还是 CASE WHEN 和 UNION ALL 比较多。;
===============================================================

行列转换练习：
行转列
select student,'chinese' course,chinese from score_2
union
select student,'math' course,math from score_2
union
select student,'english' course,english from score_2;

列转行
select student,sum(case when course='CHINESE' THEN score ELSE 0 END) CHINESE,
               sum(case when course='MATH' THEN score ELSE 0 END) MATH,
               sum(case when course='ENGLISH' THEN score ELSE 0 END) ENGLISH
from score_1 Group by student;

SELECT * FROM SCORE_2;  
SELECT * FROM SCORE_1;

select * from score_1 pivot(sum(score) for course in ('CHINESE' as CHINESE,'ENGLISH' as ENGLISH,'MATH' as MATH));
select * from score_1 pivot(max(score) for course in ('CHINESE','ENGLISH','MATH'))
SELECT * FROM SCORE_2 UNPIVOT(SCORE FOR COURSE IN (CHINESE,ENGLISH,MATH));