Oracle SQL精妙SQL语句讲解

原创 2007年09月18日 14:53:00

 

 

 

 

--行列转换 行转列
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 12;

--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||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 12;


-- 实现一条记录根据条件多表插入
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, 3over(ORDER BY a.sal DESC) lead_3
FROM scott.emp a
ORDER BY a.sal DESC;
 

SQL精妙语句讲解(Oracle)

  • 2012年12月02日 22:08
  • 29KB
  • 下载

Oracle: SQL精妙SQL语句讲解

一、重复操作查询 --where条件得distinct systemdicid作为唯一标识 select *   from dmis_zd_systemdic t WHERE typeid =...

Oracle SQL精妙SQL语句讲解

--行列转换 行转列 DROP TABLE t_change_lc; CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, ...

Oracle: SQL精妙SQL语句讲解(常用sql)

一、重复操作查询 --where条件得distinct systemdicid作为唯一标识 select *   from dmis_zd_systemdic t  WHERE typeid ...
  • Hollboy
  • Hollboy
  • 2012年04月26日 15:49
  • 451

Oracle: SQL精妙SQL语句讲解(常用sql) .

一、重复操作查询 --where条件得distinct systemdicid作为唯一标识 select *   from dmis_zd_systemdic t  WHERE typeid ...
  • azhegps
  • azhegps
  • 2016年06月09日 16:44
  • 130

Oracle: SQL精妙SQL语句讲解(常用sql) .

一、重复操作查询 --where条件得distinct systemdicid作为唯一标识 select *   from dmis_zd_systemdic t  WHERE typeid ...

精妙sql语句收集~~~~

  • 2011年03月16日 15:11
  • 45KB
  • 下载

精妙的SQL语句

  • 2007年10月08日 17:30
  • 78KB
  • 下载

[MySQL]精妙SQL语句,基础,提升,技巧

一、基础 1、说明:创建数据库 Create DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server...

精妙SQL语句收集

  • 2008年03月06日 16:49
  • 5KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle SQL精妙SQL语句讲解
举报原因:
原因补充:

(最多只允许输入30个字)