- 浏览: 119973 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (106)
- java并发 (8)
- Oracle (13)
- java基础 (11)
- Hibernate (3)
- j2ee (6)
- Spring (2)
- Linux下 (4)
- 工具 (2)
- Tomcat (2)
- english (2)
- html (3)
- 数据结构与算法 (1)
- MySQL (2)
- database (4)
- javascript && Dom (4)
- C语言&jni (2)
- protocols (1)
- 记事 (11)
- 操作系统原理(linux) (0)
- java7 (1)
- xml&xsl (3)
- mybatis (1)
- webService之cxf (0)
- web service (2)
- Android (3)
- jquery (2)
- 云计算 (0)
- NoSQL (3)
- hadoop教程 (0)
- C++ (3)
- java nio (0)
- 设计模式 (0)
最新评论
-
yuxiaojie_2013:
html5 -
BBjava:
spiniper 写道一般利用反射获去获取父类并不是一件常用的 ...
利用反射获取父类,接口信息 -
spiniper:
一般利用反射获去获取父类并不是一件常用的功能判断一个对象是否属 ...
利用反射获取父类,接口信息 -
BBjava:
_荆棘鸟_ 写道hi,你这篇blog是最经写的吗?我怎么感觉最 ...
使用eclipse进行c++的开发 -
_荆棘鸟_:
hi,你这篇blog是最经写的吗?我怎么感觉最新版的Mingw ...
使用eclipse进行c++的开发
--行列转换 行转列 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; -- 模拟批量提交http://blog.knowsky.com/ 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;
转载:应原有者的要求没有去掉他的广告,抱歉!
文章整理:西部数码--专业提供域名注册、虚拟主机服务
http://www.west263.com以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息,谢谢!
发表评论
-
oracle安装成功后,要做的一些工作
2012-11-14 11:24 0一、1.以DBA的身分进入建立user:xxxx,pwd:xx ... -
大数据量(千万级)查询优化实践经验
2011-09-01 14:04 2011首先,一个表数据超过8千万条,而且还在以每5分钟5000条记录 ... -
海量数据查询优化技巧
2011-08-30 10:56 965不可多得的经验总结来 ... -
jdbc大数据量查询优化(转)
2011-08-30 10:52 3404大略的看了一下觉得编 ... -
oracle的SQL经典练手
2011-08-10 22:40 547觉得够经典,转过来学习。 http://database.51 ... -
高级sql训练题
2011-08-09 18:07 1507本文来自百度空间:http://hi.baidu.com/yh ... -
ORACLE系统表处理
2011-07-20 10:44 865本文来自:http://shadamu.blogbus.com ... -
USER_TAB_COLUMNS(COLS)
2011-07-20 10:40 158845.4.4 列:USER_TAB_COLUMNS(C ... -
Oracle常用系统信息表 .
2011-07-20 10:23 1276本文来自open-source China开源中国:http: ... -
关于Oracle表及字段的注释
2011-07-19 16:11 858本文引自csdn:http://blog.csdn.net/z ... -
Oracle常用数据字典查询语句
2011-07-19 14:31 745Oracle常用数据字典查询语句 本文来自csdn的lius ... -
oracle相关总结(转)
2011-06-12 11:16 741文章来自:http://database. ... -
rownum in ORACLE
2011-06-01 16:53 1156ORACLE中的rownum 本文 ...
相关推荐
Oracle+SQL精妙SQL语句讲解,对学习oracle技巧很有帮助
OracleSQL精妙SQL语句讲解[定义].pdf
转发的,SQL精妙语句讲解(Oracle),很好的Oracle资料哦,有需要的朋友可以看看哟
Oracle精妙的sql语句,对开发人员大有帮助
这是一套关于SQL语句性能调整原则,SQL语句和存储过程,Oracle SQL精妙SQL语句讲解,感觉还不错
精妙SQL语句,包含所有的SQL语句代码和示例。
精妙SQL语句.txt
从日常的工作中总结的介绍oracle sql的高级写法,关联方法等
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
随机选择数据库记录的方法(使用Randomize函数,通过SQL语句实现) 对存储在数据库中的数据来说,随机数特性能给出上面的效果,但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决...
想写很精妙的SQL语句,就下载这个吧,毕备
经典SQL语句大全 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice ...
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...