Oracle SQL精妙SQL语句讲解
更新时间:2024-02-02 19:07:01 阅读量: 教育文库 文档下载
- oracle推荐度:
- 相关推荐
Oracle SQL精妙SQL语句讲解 2008-11-19 15:57 --行列转换 行转列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4 UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;
SELECT * FROM t_change_lc;
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;
--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;
SELECT * FROM t_change_cl;
SELECT t.card_code, t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;
--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;
SELECT * FROM t_change_lc_comma;
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q FROM (SELECT a.card_code, a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1 START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code;
--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q FROM (SELECT a.card_code, a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1 START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code;
SELECT * FROM t_change_cl_comma;
SELECT t.card_code, substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) > 0) t ORDER BY 1, 2;
-- 实现一条记录根据条件多表插入 DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5; DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10)); DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10)); DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));
SELECT * FROM t_ia_src; SELECT * FROM t_ia_dest_1; SELECT * FROM t_ia_dest_2; SELECT * FROM t_ia_dest_3;
INSERT ALL
WHEN (c1 IN ('a1','a3')) THEN
INTO t_ia_dest_1(flag,c) VALUES(flag1,c2) WHEN (c1 IN ('a2','a4')) THEN
INTO t_ia_dest_2(flag,c) VALUES(flag2,c2) ELSE
INTO t_ia_dest_3(flag,c) VALUES(flag1||flag2,c1||c2) SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;
-- 如果存在就更新,不存在就插入用一个语句实现 DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));
SELECT * FROM t_mg;
MERGE INTO t_mg a
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b ON (a.code = b.code) WHEN MATCHED THEN
UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN
INSERT (code, NAME) VALUES (b.code, b.NAME);
-- 抽取/删除重复记录
DROP TABLE t_dup;
CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code,
dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=10; INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=2;
SELECT * FROM t_dup;
SELECT * FROM t_dup a WHERE a.ROWID <> (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);
SELECT b.code, b.NAME FROM (SELECT a.code, a.NAME,
row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn
FROM t_dup a) b WHERE b.rn > 1;
-- IN/EXISTS的不同适用环境 -- t_orders.customer_id有索引 SELECT a.*
FROM t_employees a
WHERE a.employee_id IN
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);
SELECT a.*
FROM t_employees a
WHERE EXISTS (SELECT 1 FROM t_orders b
WHERE b.customer_id = 12
AND a.employee_id = b.sales_rep_id);
-- t_employees.department_id有索引 SELECT a.*
FROM t_employees a
WHERE a.department_id = 10 AND EXISTS
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);
SELECT a.*
FROM t_employees a
WHERE a.department_id = 10
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);
-- FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn, dbms_random.STRING('z',10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;
CREATE INDEX idx_nonfbi ON t_fbi(dt);
DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));
SELECT * FROM t_fbi WHERE trunc(dt) = to_date('2006-09-21','yyyy-mm-dd') ;
-- 不建议使用
SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';
-- LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;
SELECT * FROM t_loop;
-- 逐行提交 DECLARE BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; COMMIT; END LOOP; END;
-- 模拟批量提交 DECLARE
v_count NUMBER; BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; v_count := v_count + 1; IF v_count >= 100 THEN COMMIT; END IF;
END LOOP; COMMIT; END;
-- 真正的批量提交 DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects%ROWTYPE; recs rec; BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT INTO recs LIMIT 100; -- forall 实现批量
FORALL i IN 1 .. recs.COUNT
INSERT INTO t_loop VALUES recs (i); COMMIT;
EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur; END;
-- 悲观锁定/乐观锁定 DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT 1 ID FROM dual;
SELECT * FROM t_lock;
-- 常见的实现逻辑,隐含bug DECLARE
v_cnt NUMBER; BEGIN
-- 这里有并发性的bug
SELECT MAX(ID) INTO v_cnt FROM t_lock;
-- here for other operation v_cnt := v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt); COMMIT; END;
-- 高并发环境下,安全的实现逻辑
DECLARE
v_cnt NUMBER; BEGIN
-- 对指定的行取得lock
SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE; -- 在有lock的情况下继续下面的操作
SELECT MAX(ID) INTO v_cnt FROM t_lock;
-- here for other operation v_cnt := v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt); COMMIT; --提交并且释放lock END;
-- 硬解析/软解析
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);
SELECT * FROM t_hard;
DECLARE
sql_1 VARCHAR2(200); BEGIN
-- hard parse
-- java中的同等语句是 Statement.execute() FOR i IN 1 .. 1000 LOOP
sql_1 := 'insert into t_hard(id) values(' || i || ')'; EXECUTE IMMEDIATE sql_1; END LOOP; COMMIT;
-- soft parse
--java中的同等语句是 PreparedStatement.execute() sql_1 := 'insert into t_hard(id) values(:id)'; FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE sql_1 USING i; END LOOP; COMMIT; END;
-- 正确的分页算法
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM <= 500) WHERE rn > 480 ;
-- 分页算法(why not this one) SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM <= 500 AND ROWNUM > 480;
-- 分页算法(why not this one) SELECT b.*
FROM (SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM < = 500 ORDER BY first_name) b WHERE b.rn > 480;
-- OLAP
-- 小计合计 SELECT CASE
WHEN a.deptno IS NULL THEN '合计'
WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN '小计' ELSE
'' || a.deptno END deptno, a.empno, a.ename,
SUM(a.sal) total_sal FROM scott.emp a
GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());
-- 分组排序
SELECT a.deptno, a.empno, a.ename, a.sal,
-- 可跳跃的rank
rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1, -- 密集型rank
dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
-- 不分组排序
rank() over(ORDER BY sal DESC) r3 FROM scott.emp a
ORDER BY a.deptno,a.sal DESC;
-- 当前行数据和前/后n行的数据比较 SELECT a.empno, a.ename, a.sal,
-- 上面一行
lag(a.sal) over(ORDER BY a.sal DESC) lag_1, -- 下面三行
lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3 FROM scott.emp a
ORDER BY a.sal DESC;
正在阅读:
Oracle SQL精妙SQL语句讲解02-02
初中英语牛津深圳版《八年级下(旧)》《Chapter 3 Family life》精选课后测试10-27
2017年不间断电源(UPS)现状及发展趋势分析 (目录)03-28
关于设置骨科医院的可行性研究报告(2)10-14
建党90周年活动方案09-11
中央纪委关于“不准在领导干部管辖的业务范围内个人从事可能与公05-01
透视学教学大纲09-11
八年级下期语文校本教材练习资料312-14
楚香凝2016江苏B类行测数量真题解析11-11
- exercise2
- 铅锌矿详查地质设计 - 图文
- 厨余垃圾、餐厨垃圾堆肥系统设计方案
- 陈明珠开题报告
- 化工原理精选例题
- 政府形象宣传册营销案例
- 小学一至三年级语文阅读专项练习题
- 2014.民诉 期末考试 复习题
- 巅峰智业 - 做好顶层设计对建设城市的重要意义
- (三起)冀教版三年级英语上册Unit4 Lesson24练习题及答案
- 2017年实心轮胎现状及发展趋势分析(目录)
- 基于GIS的农用地定级技术研究定稿
- 2017-2022年中国医疗保健市场调查与市场前景预测报告(目录) - 图文
- 作业
- OFDM技术仿真(MATLAB代码) - 图文
- Android工程师笔试题及答案
- 生命密码联合密码
- 空间地上权若干法律问题探究
- 江苏学业水平测试《机械基础》模拟试题
- 选课走班实施方案
- 精妙
- 语句
- 讲解
- SQL
- Oracle
- 2006年1月自学考试机关管理试题
- 河南专升本语法(一)动词时态语态(学生版)
- 滴露洗手液市场营销推广活动策划方案
- 监管方式 征免性质 征减免税方式代码说明
- 北段起点~金山寺站工程施工监理实施细则(1部分)已改
- PYANG工具使用说明
- 中小学德育工作调研参考提纲1
- 国际民航三大公约
- 案例分析:中国邮政速递服务公司的市场定位
- 3.1 自然资源 教案(商务星球版八年级上) - 图文
- 光伏并网发电模拟装置
- 我的师德追求-国培
- 文学理论期末考点
- 资源枯竭型城市的转型问题
- 2018年山东省执业药师继续教育试题答案
- 最新湖南省2018-2019年四年级下数学期末质量检测试题(1)
- 高三物理(人教版 东部)一轮复习备考:第八单元 曲线运动、万有引力与机械能综合(教师用卷)
- 高校邦创业基本功与精益创业方法论答案2016
- 二年级数学奥数题综合练习题大全23
- VAV VRV VWV空调系统