使用with复用查询结果集及递归查询
适用于一个复杂SQL中多次使用同一个结果集，但是在数据库中只查询一次。
WITH
T1 AS (SELECT * FROM EMP),
T2 AS (SELECT * FROM T1 WHERE DEPTNO=10),
T3 AS (SELECT * FROM T2 WHERE SAL>1500)
SELECT * FROM T3
将列转成行
# WM_CONCAT只支持以逗号分隔。 且oracle 12c之后已废除
SELECT WM_CONCAT(DISTINCT dname) FROM dept;
# LISTAGG无版本限制
SELECT DEPTNO, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS empnames FROM emp GROUP BY DEPTNO ORDER BY 1;
# 19c新特性，还支持listagg内部使用distinct去重
SELECT DEPTNO, LISTAGG(DISTINCT ename, ',') WITHIN GROUP (ORDER BY ename) AS empnames FROM emp GROUP BY DEPTNO ORDER BY 1;
并发执行
SELECT /*+ parallel(d, 8) */ * FROM dept d WHERE 1=1;
SELECT /*+ parallel(d, 8) parallel(e, 8) */ * FROM dept d
INNER JOIN emp e
ON d.deptno=e.deptno
WHERE 1=1;
INSERT /*+ parallel(d, 8) */ INTO dept d SELECT * FROM dept1;
DELETE /*+ parallel(d, 8) */ FROM dept d WHERE 1=1;
UPDATE /*+ parallel(d, 8) */ dept d SET dept_stat='1' WHERE 1=1;
表关联更新 MERGE INTO
将表T的某一列值，根据关联条件更新为表S的某一列值
MERGE INTO TARGET_TB1 T USING SOURCE_TB2 S ON (T.ID = S.AID)
WHEN MATCHED THEN UPDATE SET T.COL1 = S.COL2, T.COL2 = S.COL4;
以上SQL等同于
UPDATE TARGET_TB1 T SET (T.COL1, T.COL2)=(SELECT COL2, COL4 FROM SOURCE_TB2
WHERE AID=T.ID) WHERE T.ID IN (SELECT AID FROM SOURCE_TB2);
此外，MERGE INTO还支持UPDATE同时DELTE和INSERT
MERGE INTO TARGET_TB1 T USING SOURCE_TB2 S ON (T.ID = S.AID)
WHEN MATCHED THEN UPDATE SET T.COL1 = S.COL2, T.COL2 = S.COL4
DELETE WHERE T.ID=123456
WHEN NOT MATCHED THEN INSERT (ID, COL1, COL2, COL3)
VALUES (S.AID, S.COL2, S.COL4, 'XXX');
组内排序，取组内序号
SELECT empno, ename, deptno, sal,
ROW_NUMBER () OVER(PARTITION BY deptno ORDER BY sal ASC) 工资排行
FROM emp WHERE 1=1;
组内排序，取组内最大值与最小值
SELECT empno, sal, deptno,
row_number() OVER(PARTITION BY deptno ORDER BY sal ASC) 组内工资排行,
MIN(sal) OVER(PARTITION BY deptno ) 组内最低工资,
MAX(sal) OVER(PARTITION BY deptno ) 组内最高工资
FROM emp
case when
SELECT empno, ename, deptno, sal,
CASE WHEN sal<1000 THEN '低'
WHEN sal>1001 and sal<3000 THEN '中'
ELSE '高' END AS 薪资水平,
CASE deptno WHEN 10 THEN '财务科'
WHEN 20 THEN '人事科'
ELSE '打工狗' END AS 部门
FROM emp WHERE 1=1;
日期与时间戳转换
SELECT TO_CHAR(
(TO_TIMESTAMP('1970-01-01 08:00:00.000000', 'yyyy-MM-dd HH24:MI:SS.ff6')
+ START_TM/(1000 * 60 * 60 * 24)),
'yyyy-MM-dd HH24:MI:SS')
FROM TBL_TASK;
注：oracle中的除默认是带小数位的，所以这里除了天(1000 * 60 * 60 * 24)之后，还能精确到微秒。
日期与字符串格式转换
SELECT TO_CHAR(sysdate,'YYYYMMDD HH24:MI:SS'),
TO_DATE('20220417 23:32:32','YYYYMMDD HH24:MI:SS')
FROM dual;
日期格式说明
| 格式 | 说明 |
|---|---|
| D | 一周中的星期几 |
| DAY | 天的名字，使用空格填充到9个字符 |
| DD | 月中的第几天 |
| DDD | 年中的第几天 |
| DY | 天的简写名 |
| IW | ISO标准的年中的第几周 |
| IYYY | ISO标准的四位年份 |
| YYYY | 四位年份 |
| YYY,YY,Y | 年份的最后三位，两位，一位 |
| HH | 小时，按12小时计 |
| HH24 | 小时，按24小时计 |
| MI | 分 |
| SS | 秒 |
| MM | 月 |
| Mon | 月份的简写 |
| Month | 月份的全名 |
| W | 该月的第几个星期 |
| WW | 年中的第几个星期 |
日期加减
--3分钟前
select sysdate,sysdate - interval '3' MINUTE from dual;
--3小时前
select sysdate - interval '3' hour from dual;
--3天后
select sysdate + interval '3' day from dual;
--按天计算可以直接使用加减号计算 3天后如下
select sysdate + 3 from dual;
--300天前
select sysdate - 300 from dual;
--3个月后
select sysdate,sysdate + interval '3' month from dual;
select sysdate,add_months(sysdate,3) from dual;
--3*3年前
select sysdate,sysdate - 3*interval '3' year from dual;
锁表处理
--1：查看被锁记录
SELECT object_name,machine,s.sid,s.serial#
FROM v$locked_object l,dba_objects o ,v$session s
WHERE l.object_id = o.object_id AND l.session_id=s.sid
--删除或解锁该记录
ALTER system KILL SESSION 'sid,serial#';
ALTER system KILL SESSION '1,1212';
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--生成杀掉进程SQL
SELECT 'alter system kill session '''||a.sid||','||a.serial#||''';'
FROM v$process p,v$session a, v$locked_object b,all_objects c
WHERE p.addr=a.paddr AND a.process=b.process
AND c.object_id=b.object_id;
查看数据库最近执行的SQL
SELECT t.SQL_TEXT, t.FIRST_LOAD_TIME FROM v$sqlarea t ORDER BY 2 DESC;
NOWAIT锁表查询
--nowait方式查询，获取不到锁时直接报错
-- ORA-00054：资源正忙，但指定以 NOWAIT 方式获取资源，或者超时失效
SELECT * FROM emp WHERE id=1001 FOR UPDATE NOWAIT;
WAIT等待指定时间锁表查询
--WAIT方式查询，等待2秒，2秒内获取不到锁时直接报错
-- ORA-30006：资源已被占用；执行操作时出现 WAIT 超时
-- 本次结果测试版本11g
SELECT * FROM emp WHERE id=1001 FOR UPDATE WAIT 2;
查看oracle版本号
SELECT * FROM v$version;
DML增删改
INSERT INTO table_name (col1, col2...) VALUES (value1, value2...);
INSERT INTO table_name VALUES (value1, value2...);
UPDATE table_name SET col1=222,col2=333 WHERE condition;
DELETE FROM tablename WHERE condition;
DDL增删改
ALTER TABLE table_name MODIFY col_name null;
ALTER TABLE table_name RENAME COLUMN col_name1 TO col_name2;
ALTER TABLE table_name DROP COLUMN col_name;
--drop多列
ALTER TABLE table_name DROP (col1_name, col2_name, col3_name);
ALTER TABLE table_name ADD (col_name VARCHAR(10));
ALTER TABLE table_name MODIFY (col_name VARCHAR2(16));
CREATE TABLE table_name (
col_name NUMBER(2) NOT NULL,
col2_name VARCHAR2(14),
col3_name VARCHAR2(13),
CONSTRAINT pk_table PRIMARY KEY (col_name)
);
索引
CREATE UNIQUE INDEX idx_name ON table_name (col_name DESC, col_name2, col_name3);
数据库操作
database dbname
按月统计日均余额SQL
SQL作用是按月统计日均余额
WITH
T AS
(SELECT '001' ACC, TO_DATE('2022/8/18', 'yyyy/mm/dd') START_DT, 100 BAL_AMT FROM DUAL
UNION ALL SELECT '001', TO_DATE('2022/10/3', 'yyyy/mm/dd'), 200 FROM DUAL
UNION ALL SELECT '001', TO_DATE('2022/10/5', 'yyyy/mm/dd'), 400 FROM DUAL
UNION ALL SELECT '001', TO_DATE('2022/10/21', 'yyyy/mm/dd'), 100 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2022/10/6', 'yyyy/mm/dd'), 500 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2022/10/8', 'yyyy/mm/dd'), 100 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2022/10/14', 'yyyy/mm/dd'), 300 FROM DUAL
UNION ALL SELECT '002', TO_DATE('2022/10/24', 'yyyy/mm/dd'), 100 FROM DUAL),
T1 AS
(SELECT ACC, TO_DATE('2022/10/1', 'yyyy/mm/dd') + LEVEL - 1 START_DT
FROM (SELECT DISTINCT ACC FROM T)
CONNECT BY LEVEL <= 31
AND PRIOR ACC = ACC
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL),
T2 AS (SELECT T1.ACC, T1.START_DT, T.BAL_AMT
FROM T
RIGHT JOIN T1
ON T.START_DT = T1.START_DT
AND T.ACC = T1.ACC
UNION ALL
SELECT * FROM T
WHERE START_DT NOT IN (SELECT DISTINCT START_DT FROM T1))
SELECT '2022/10' ver_month, ACC, ROUND(AVG(BAL_AMT), 2) YUE
FROM (SELECT ACC,
START_DT,
NVL(NVL(BAL_AMT,
LAST_VALUE(BAL_AMT IGNORE NULLS)
OVER(PARTITION BY ACC ORDER BY START_DT)),
0) BAL_AMT
FROM T2
--ORDER BY 1, 2
)
WHERE START_DT IN (SELECT DISTINCT START_DT FROM T1)
GROUP BY ACC
ORDER BY 2
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
