Oracle 函数扩展二

行转列/列转行

PIVOT(聚合函数(列) FOR 列 in (…) ) 行转列

WITH TEMP_TABLE AS (
     SELECT '12楼' FLOOR,'a' NAME, '10' AGE,'11' NB FROM DUAL
     UNION ALL
     SELECT '12楼' FLOOR,'a' NAME, '20' AGE,'11' NB FROM DUAL
     UNION ALL
     SELECT '12楼' FLOOR,'b' NAME, '30' AGE,'12' NB FROM DUAL
     UNION 
     SELECT '12楼' FLOOR,'c' NAME, '40' AGE,'13' NB FROM DUAL
     UNION
     SELECT '19楼' FLOOR,'d' NAME, '20' AGE,'14' NB FROM DUAL
)
--除去AGE和NAME字段之外的字段分组，把NAME字段的值等于IN()里面的值进行“行转列”，之后再根据前面的聚合函数计算
SELECT * FROM TEMP_TABLE PIVOT(SUM(AGE) FOR NAME IN ('a','b'))

LISTAGG() WITHIN GROUP () 行转列（多行合并一行）

WITH TEMP_TABLE AS (
     SELECT '12楼' FLOOR,'a' NAME, '10' AGE,'11' NB FROM DUAL
     UNION ALL
     SELECT '12楼' FLOOR,'a' NAME, '20' AGE,'11' NB FROM DUAL
     UNION ALL
     SELECT '12楼' FLOOR,'b' NAME, '30' AGE,'12' NB FROM DUAL
     UNION 
     SELECT '12楼' FLOOR,'c' NAME, '40' AGE,'13' NB FROM DUAL
     UNION
     SELECT '19楼' FLOOR,'d' NAME, '20' AGE,'14' NB FROM DUAL
)
--该函数必须配合 GROUP BY 使用，根据FLOOR为主，NAME字段的值多行转为一行（行转列）
SELECT FLOOR,LISTAGG(NAME, ',') WITHIN GROUP(ORDER BY NAME) AS NAMES FROM TEMP_TABLE GROUP BY FLOOR

UNPIVOT ( V FOR K IN (A, B, C)) 列转行

WITH TEMP_TABLE AS (
     SELECT '12楼' FLOOR,'a' NAME, '10' AGE,'11' NB FROM DUAL
     UNION ALL
     SELECT '19楼' FLOOR,'d' NAME, '20' AGE,'14' NB FROM DUAL
)
--把一行数据根据某个字段为主，其他字段转化成与之对应的数据
SELECT FLOOR,ZiDuan,Zhi FROM TEMP_TABLE UNPIVOT (Zhi FOR ZiDuan IN (NAME, AGE, NB))

分析函数

OVER( PARTITION BY 字段 ORDER BY 字段)

• OVER() 前面须是聚合函数，和GROUP BY 的区别是，GROUP BY 之后只有一条数据，OVER()后原数据条数不变，多了一个分析字段

WITH TEMP_TABLE AS (
     SELECT '12楼' FLOOR,'a' NAME, '20' AGE FROM DUAL
     UNION ALL
     SELECT '12楼' FLOOR,'b' NAME, '30' AGE FROM DUAL
     UNION 
     SELECT '12楼' FLOOR,'c' NAME, '40' AGE FROM DUAL
     UNION
     SELECT '19楼' FLOOR,'d' NAME, '20' AGE FROM DUAL
)
--根据floor分组，后count()统计；
SELECT NAME,FLOOR,COUNT(*)OVER(PARTITION BY FLOOR) FROM TEMP_TABLE
--PARTITION BY 和 ORDER BY 如果字段不一致，则是根据 ORDER BY 后面的字段排序之后，每条数据累加
--PARTITION BY 和 ORDER BY 如果字段一致，则加不加 ORDER BY 没区别
SELECT NAME,FLOOR,COUNT(*)OVER(PARTITION BY FLOOR ORDER BY NAME) FROM TEMP_TABLE
--COUNT()也可以换成SUM(),MAX(),MIN()等其他聚合函数;SUM()中的字段可以和COUNT()中的字段不同
SELECT NAME,FLOOR,SUM(AGE)OVER(PARTITION BY FLOOR) FROM TEMP_TABLE

过滤数据:

EXISTS()

• EXISTS() 括号中的sql有查到数据，则查询 EXISTS 之前的sql；如果括号中sql没有查到数据，则不会执行整个sql。

--例子一：只会展示table1中id=1的数据
WITH TABLE1 AS (
     SELECT '1' AS ID FROM DUAL
     UNION ALL
     SELECT '11' AS ID FROM DUAL
),
TABLE2 AS (
     SELECT '1' AS ID FROM DUAL
)
SELECT * FROM TABLE1 WHERE EXISTS (SELECT * FROM TABLE2 WHERE TABLE1.ID=TABLE2.ID)
--例子二：无数据
WITH TABLE1 AS (
     SELECT '1' AS ID FROM DUAL
     UNION ALL
     SELECT '11' AS ID FROM DUAL
),
TABLE2 AS (
     SELECT '2' AS ID FROM DUAL
)
SELECT * FROM TABLE1 WHERE EXISTS (SELECT * FROM TABLE2 WHERE TABLE1.ID=TABLE2.ID)

NOT EXISTS()

• 和exists() 完全相反。

ANY

• any 就是匹配集合中的任意一个就满足条件了。

--table1 的每条数据，只要qty > 括号中数据的任意一条，Table1 中的该条数据就满足
SELECT * FROM TABLE1 WHERE qty > ANY (SELECT qty FROM table2)

ALL

• all 要跟所有的都比较，所有都满足以后才为真。

--table1 的每条数据，只要qty < 括号中的全部数据，Table1 中的该条数据才满足
SELECT * FROM TABLE1 WHERE qty < ALL (SELECT qty FROM table2)  ORDER BY qty;

SOME

• 等同于 IN

SELECT * FROM TABLE1 WHERE qty = SOME (SELECT qty FROM table2)  ORDER BY qty;

设置操作符:

UNION

• 把两个sql的结果集合并在一起，并且去重。

UNION ALL

• 把两个sql的结果集合并在一起，不去重。

INTERSECT

• 把两个sql的结果集合并在一起，并且取交集去重。（交集）

MINUS

• 前面的结果集中减去后面结果集之后剩下的数据，并去重。（差集）

插入数据：

INSERT ALL

• 讨论多重插入语句，将多行插入到一个或多个表中。
• 例如从一张表查出来的数据要存到两张表里，普通的insert是插入两次，如果同时源表的数据改变，两张表的数据可能会出现不一致。

--无条件写数据的情况
insert all into t1 (object_name, object_id) into t2 (object_name, object_id) 
select * from t;
--有条件写数据的情况
insert all when object_id < 5 then into t1 (object_name, object_id) 
		   when object_id >= 5 then into t2 (object_name, object_id)
select * from t;

INSERT FIRST

• 符合条件的同样会写数据，不过已经存在数据了，insert first是不会写入的。

CONNECT BY LEVEL

• 伪列，需配合 LEVEL 一起使用

--20210301到20210312之间除去周六周末有几天
SELECT COUNT(1)
FROM (SELECT TO_CHAR(TO_DATE('20210312', 'yyyymmdd') - LEVEL, 'd') DOW
FROM DUAL CONNECT BY LEVEL <= TRUNC(TO_DATE('20210312', 'yyyymmdd') - TO_DATE('20210301','yyyyMMdd')))
WHERE DOW NOT IN (7, 1)
--得到伪列，日期减去伪列就是对应的日期，TO_CHAR(DATE, 'D') 得到该日期是周几
SELECT LEVEL
FROM DUAL CONNECT BY LEVEL <= TRUNC(TO_DATE('20210312', 'yyyymmdd') - TO_DATE('20210301','yyyyMMdd'))