ITPub博客

首页 > 数据库 > Oracle > 9.子查询

9.子查询

原创 Oracle 作者:zuiai510416 时间:2014-02-11 19:55:22 0 删除 编辑
--subquery子查询


--single-row subqueries 单行子查询
Select Sysdate From dual;
--multiple-row subqueries 多行子查询
Create Table t As Select * From tt;
Select * From tt Where id In (Select Distinct Id From t);
--any
Select * From tt Where id > Any (Select Distinct Id From t);
Select * From tt Where id > (Select Min(Id) From t);
--multiple-column subquery多列子查询
Select * From t Where (Id,Name) In (Select Id,Name From tt);
Insert Into t Select Id,(Select 2000 From dual) From tt;
--======================
--相关子查询
Select t.Id,t.Name,t.name1 From t Where Id > (Select Avg(Id) From t);
--相关子查询 配合update,delete使用
Select * From t;
Update t a Set a.Name= 'bigger than avg' Where a.Id > (Select Avg(Id) From t Where t.Id = a.Id);
--exists
Select * From t Where Exists(Select 1 From tt Where tt.Id = t.Id);  --exists为true的行返回
/*
exists和in的比较,2个表一样大小时,差别不大
子表大时,用exists,子表小时,用in
*/
Create Table t2 As Select * From dba_objects
Create Table t3 As Select * From DBA_STORED_SETTINGS
Select Count(*) From t2 Where object_id In (Select object_id From t3);                             --set timing on;   0.031
Select Count(*) From t2 Where Exists (Select 1 From t3 Where t2.object_id = t3.object_id);         --set timing on;   0.031
Select /*+ ordered use_nl(t2 t3) */ Count(*) From t2 Where object_id In (Select object_id From t3); --强制使用循环排序算法 set timing on;   1.123 改变了执行计划
--WITH 简化复杂处理,效果比较好
With
    TEMP1 As (Select * From T),
    TEMP2 As (Select * From TT)
    Select TEMP1.*,TEMP2.* From TEMP1,TEMP2 Where TEMP1.Id = TEMP2.Id

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15810196/viewspace-1079754/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 8.join连接
下一篇: 10.schema object
请登录后发表评论 登录
全部评论

注册时间:2011-07-19

  • 博文量
    53
  • 访问量
    131745