精华内容
下载资源
问答
  • Oracle多表查询,四种连接方式

    万次阅读 多人点赞 2018-03-22 09:26:24
    二、多表查询:  表连接分类: 内连接、外连接、交叉连接、自连接   1、内连接: [inner] join on  SQL语法格式:  语法1:  select *  from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;  语法2:...

        一、聚合函数:(都会忽略null数据)
            1、常用的有5种:将字段中所有的数据聚合在一条中
                1、sum(字段名)        :求总和    
                2、avg(字段名)        :求平均值
                3、max(字段名)        :求最大值
                4、min(字段名)        :求最小值
                5、count(字段名、*)    :统计行数  
            2、按部门编号,查询平均薪水 ,并且平均薪水<1300的不显示,结果按降序排序
                select empno,avg(sal) as avgsal
                from scott.emp
                group by empno
                having avg(sal)>=1300
                order by avgsal desc;

            说明:
                SQL语句的执行顺序:一定是从上到下的!   
                group by 执行之后才会执行having、select中没有使用聚合函数的字段名必须写在这里
                having 后不能使用字段的别名、可以聚合函数、一般字段名
                order by 后面可以有:字段名、聚合函数、字段别名

            注意:
                Oracle 10g中才会出现的容错性:
                    having 语句可以写在group by 之前,不会报错,但是实际上执行的顺序还是先执行group by 后执行having子句

            说明:
                1、当一个查询中,出现聚合函数和没有使用聚合函数的字段,则该字段必须出现在group by子句中!
                2、group by 字段1,字段2;  会先按照字段1分组得到一个结果集,再按照字段2进行分组!
                3、where 发生在group by 前!
                4、where 后面不能有聚合函数!

        二、多表查询:
                表连接分类: 内连接、外连接、交叉连接、自连接
                1、内连接: [inner] join    on
                    SQL语法格式:
                        语法1:
                            select *
                            from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;
                        语法2:
                            select *
                            from 表1,表2
                            where 表1.字段1=表2.字段1;

                    说明: 
                        内连接中的inner join 和 join 是等价的!但是建议为了程序的可读性
                        尽量不要省略inner!

                2、外连接:         
                    分类:左外连接、右外连接、全连接!
                    1)、左外连接:left outer join             
                        连接效果:
                            左侧的表中的全部数据都会被显示出来,但是右侧表的数据,
                            只有和左侧匹配上的字段才会被查询出来!否则都会显示null!
                        SQL语法格式:             
                            语法1:
                                select *
                                from 表1 left outer join 表2
                                     on 表1.字段1=表2.字段1;
                            语法2:
                                select *
                                from 表1 left outer join 表2
                                where 表1.字段1=表2.字段1(+);

                    2)、右外连接:right outer join           
                        连接效果:
                            右侧的表中的全部数据都会被显示出来,但是左侧表的数据,
                            只有和右侧匹配上的字段才会被查询出来!否则都会显示null!
                        SQL语法格式:              
                            语法1:
                                select *
                                from 表1 right outer join 表2
                                     on 表1.字段1=表2.字段1;
                            语法2:
                                select *
                                from 表1 left outer join 表2
                                where 表1.字段1(+)=表2.字段1;

                    3)、全外连接:full/all outer join 
                        SQL语法格式:              
                                select *
                                from 表1 full outer join 表2
                                     on 表1.字段1=表2.字段1;

                3、自连接(self join)
                        自连接(self join)是SQL语句中经常要用的连接方式,使用自连接可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。
                        示例:
                        在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理),并且每一个经理自身也是公司的员工,自身也有自己的经理。
                        但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。我们可以这样来写SQL语句:
                        SQL> select work.ename worker,mgr.ename  manager from scott.emp work, scott.emp mgr
                          2  where work.mgr = mgr.empno
                          3  order by work.ename;
                            WORKER     MANAGER
                            ---------- ----------
                            ADAMS      SCOTT
                            ALLEN      BLAKE
                            BLAKE      KING
                            CLARK      KING
                            FORD       JONES
                            JAMES      BLAKE
                            JONES      KING
                            MARTIN     BLAKE
                            MILLER     CLARK
                            SCOTT      JONES
                            SMITH      FORD

                            WORKER     MANAGER
                            ---------- ----------
                            TURNER     BLAKE
                            WARD       BLAKE

                            已选择13行。

                4、交叉连接: 表与表之间做笛卡尔积查询!
                    SQL语法格式:(无条件查询)
                        select *
                        from 表1 cross join 表2;
                               或者
                        select *
                        from 表1, 表2;

    转自:https://www.cnblogs.com/newwind/p/5677406.html 

     

    展开全文
  • oracle多表查询中的去重问题

    千次阅读 2018-10-09 16:23:41
    通常情况下distinct只能用于单表查询的去重,但是多表查询用distinct就不奏效了 业务需求:一个用户可以在同一组织中担任多个角色,但是现在只需查询组织名称,那么查询结果会有多条同结果,但是由于主表中只有...

    select O.PARTY_ORG_NAME,R.ROLE_CODE,R.ROLE_NAME 
    from T_CCPC_DB_URP T  
    left join T_CCPC_DB_ORGANIZATION O on O.PARTY_ORG_ID=T.ORG_ID 
    left join T_EAP_SYS_ROLE R on R.ROLE_ID=T.ROLE_ID 
    where USER_ID='550ab0909def442981f976af27fa4e78' and T.IS_ENABLE='1' 
    and t.rowid in
    (select min(rowid) rid
    from t_ccpc_db_urp k where k.USER_ID='550ab0909def442981f976af27fa4e78'
    group by k.org_id )
    order by O.PARTY_ORG_NO

    通常情况下distinct只能用于单表查询的去重,但是多表查询用distinct就不奏效了

    业务需求:一个用户可以在同一组织中担任多个角色,但是现在只需查询组织名称,那么查询结果会有多条同结果,但是由于主表中只有组织的id所以需要关联附表来查其名称,distinct就不适用。

    解决方案:1、在where条件中过滤掉重复的,查询的每一条数据都有一个隐藏字段rowid,通过select min(rowid) rid 表明,来获取到最rowid最小的那一条数据。2、group by k.org_id则是以org_id为判断条件来去重。3、select min(rowid) rid from t_ccpc_db_urp k where k.USER_ID='550ab0909def442981f976af27fa4e78' group by k.org_id   意义为:通过用户id为条件并以org_id分组,查询每组中最小的rowid,那么查询的结构就是某用户在每个组织中最小的rowid(用户可能在同一组织中担任多个角色,那么此分组中就会有多个rowid,用min(rowid)取组别中最小rowid中的那个,这样就去掉了相同org_id的情况)。4、以rowid为条件再加到主sql中t.rowid in()

    展开全文
  • 连接语句如下: from user a,assinfo b,payable c,gov d where a.accountno = b.account_no and b.cert_no = c....遇到的问题是:如果d没有满足条件的记录,会导致整个查询没有结果。 请问各位应该怎么解决?
  • Oracle 查询技巧与优化(二) 多表查询

    万次阅读 多人点赞 2016-08-05 08:59:59
    关于Oracle多表查询的一些技巧和优化~

    前言

    上一篇blog介绍了Oracle中的单表查询和排序的相关技巧(http://blog.csdn.net/wlwlwlwl015/article/details/52083588),本篇blog继续介绍查询中用的最多的——多表查询的技巧与优化方式,下面依旧通过一次例子看一个最简单的多表查询。

    多表查询

    上一篇中提到了学生信息表的民族代码(mzdm_)这个字段通常应该关联字典表来查询其对应的汉字,实际上我们也是这么做的,首先简单看一下表结构,首先是字典表:
    这里写图片描述

    如上图,可以看到每个民族代码和名称都是由两个字段——“itemkey_”和“itemvalue_”以键值形式对应起来的,而学生信息表只存了民族代码字段(mzdm_),所以通过mzdm_和itemkey_相对应就能很好的查询出民族对应的汉字了,比如这样写:

    select t1.*, t2.itemvalue_ mzmc_
      from (select sid_, stuname_, mzdm_ from t_studentinfo) t1
      left join (select itemkey_, itemvalue_
                   from t_dict
                  where itemname_ = 'EthnicType') t2
        on t1.mzdm_ = t2.itemkey_;
    

    接下来查看一下运行结果:
    这里写图片描述

    如上写法(左连接查询)是我在项目中运用最多的形式之一,暂不评论好坏与效率,总之查询结果是很好的展现出来了,接下来就具体研究一下多表查询的几种方式与区别。

    UNION ALL

    如题,这是我们第一个介绍的操作多表的方式就是UNION和UNION ALL,UNION和UNION ALL也是存在一定区别的,首先明确一点基本概念,UNION和UNION ALL是用来合并多个数据集的,例如将两个select语句的结果合并为一个整体:

    select bmh_, stuname_, csrq_, mzdm_
      from t_studentinfo
     where mzdm_ = 2
    union all
    select bmh_, stuname_, csrq_, mzdm_
      from t_studentinfo
     where mzdm_ = 5
    

    查询结果如下:
    这里写图片描述

    如上图所示,把mzdm_为2和5的结果集合并在了一起,那么接下来把UNION ALL换成UNION再看一下运行结果:
    这里写图片描述

    注意观察上图中的第一列BMH_不难发现,UNION进行了排序(默认规则排序,即按查询结果的首列进行排序),这就是它与UNION ALL的区别之一,再看一下下面这两个SQL和查询结果:

    select bmh_, stuname_, csrq_, mzdm_
      from t_studentinfo
     where mzdm_ in (2, 5)
       and csrq_ like '200%';
    

    运行结果如下:
    这里写图片描述

    select bmh_, stuname_, csrq_, mzdm_
      from t_studentinfo
     where mzdm_ in (2, 5)
       and csrq_ like '2001%';

    运行结果如下:
    这里写图片描述

    可以看到第二段查询结果肯定是包含在第一段查询结果之内的,那么它们进行UNION和UNION ALL又会有何区别呢?分别看一下,首先是UNION ALL:
    这里写图片描述

    如上图,不难发现使用UNION ALL查询出了上面两个结果集的总和,包括6对重复数据+5条单独的数据总共17条,那么再看看UNION的结果:
    这里写图片描述

    显而易见,和UNION ALL相比UNION帮我们自动剔除了6条重复结果,得到的是上面两个结果集的并集,同时并没有排序,这也就是UNION ALL与UNION的第二个区别了,最后简单总结一下UNION与UNION ALL的区别:

    1. UNION会自动去除多个结果集合中的重复结果,而UNION ALL则将所有的结果全部显示出来,不管是不是重复。
    2. UNION会对结果集进行默认规则的排序,而UNION ALL则不会进行任何排序。

    所以效率方面很明显UNION ALL要高于UNION,因为它少去了排序和去重的工作。当然还有一点需要注意,UNION和UNION ALL也可以用来合并不同的两张表的结果集,但是字段类型和个数需要匹配,例如:

    select sid_, stuname_, mzdm_
      from t_studentinfo
     where sid_ = '33405'
    union all
    select did_, itemvalue_, itemkey_
      from t_dict
     where did_ = '366'
    

    查看一下运行结果:
    这里写图片描述

    当数据配型不匹配或是列数不匹配时则会报错:
    这里写图片描述
    这里写图片描述

    当列数不够时完全也可以用NULL来代替从而避免上图中的错误。最后再举个例子看一下UNION ALL在某些比较有意义的场景下的作用,首先创建一张临时表:

    with test as
     (select 'aaa' as name1, 'bbb' as name2
        from dual
      union all
      select 'bbb' as name1, 'ccc' as name2
        from dual
      union all
      select 'ccc' as name1, 'ddd' as name2
        from dual
      union all
      select 'ddd' as name1, 'eee' as name2
        from dual
      union all
      select 'eee' as name1, 'fff' as name2
        from dual
      union all
      select 'fff' as name1, 'ggg' as name2
        from dual)
    select * from test;
    

    运行结果如下:
    这里写图片描述

    我们的需求也很简单,即:统计NAME1和NAME2中每个不同的值出现的次数。谈一下思路,首先统计NAME1每个值出现的次数,再统计NAME2每个值出现的次数,最后对上面两个结果集进行UNION ALL合并,最后再进行一次分组和排序即可:

    with test as
     (select 'aaa' as name1, 'bbb' as name2
        from dual
      union all
      select 'bbb' as name1, 'ccc' as name2
        from dual
      union all
      select 'ccc' as name1, 'ddd' as name2
        from dual
      union all
      select 'ddd' as name1, 'eee' as name2
        from dual
      union all
      select 'eee' as name1, 'fff' as name2
        from dual
      union all
      select 'fff' as name1, 'ggg' as name2
        from dual)
    select namex, sum(times) times
      from (select name1 namex, count(*) times
              from test
             group by name1
            union all
            select name2 namex, count(*) times
              from test
             group by name2)
     group by namex
     order by namex;
    

    运行结果如下:
    这里写图片描述

    OK,很好的完成了查询,那么关于UNION和UNION ALL暂且介绍到这里。

    是否使用JOIN

    如题,blog开头写的那个例子是使用LEFT JOIN完成两张表的关联查询的,那么另外也可以不用JOIN而通过WHERE条件来完成以达到相同的效果:

    select t1.sid_, t1.stuname_, t1.mzdm_, t2.itemvalue_ mzmc_
      from t_studentinfo t1, t_dict t2
     where t1.mzdm_ = t2.itemkey_
       and t2.itemname_ = 'EthnicType';

    运行效果如下:
    这里写图片描述

    回头看一下blog开头的SQL和运行效果,可以发现和上图一模一样,那使用哪一种更合适呢?JOIN的写法是SQL-92的标准,多表关联时候使用JOIN方式进行关联查询可以更清楚的看到各表之间的联系,也方便维护SQL,所以还是不建议上面使用WHERE的查询方式,而是应该使用JOIN的写法。

    IN和EXISTS

    如题,这也是在查询中经常用到的,尤其是IN关键字,在项目中使用的相当频繁,经常会有通过for循环和StringBuffer来拼接IN语句的写法,那么接下来就仔细看一下IN和EXISTS的使用场景以及效率问题,依旧通过举例说明,比如这个需求,查询所有汉族学生的成绩:

    explain plan for select *
      from t_studentscore
     where bmh_ in (select bmh_ from t_studentinfo where mzdm_ = 1);
    select * from table(dbms_xplan.display());

    观察一下执行计划:

    1 Plan hash value: 902966761
    2
    3 ————————————————————————————-
    4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    5 ————————————————————————————-
    6 | 0 | SELECT STATEMENT | | 535 | 37985 | 240 (1)| 00:00:03 |
    7 |* 1 | HASH JOIN | | 535 | 37985 | 240 (1)| 00:00:03 |
    8 |* 2 | TABLE ACCESS FULL| T_STUDENTINFO | 535 | 5885 | 207 (1)| 00:00:03 |
    9 | 3 | TABLE ACCESS FULL| T_STUDENTSCORE | 11642 | 682K| 32 (0)| 00:00:01 |
    10 ————————————————————————————-
    11
    12 Predicate Information (identified by operation id):
    13 —————————————————
    14
    15 1 - access(“BMH_”=SYS_OP_C2C(“BMH_”))
    16 2 - filter(“MZDM_”=1)

    同理,将IN换成EXISTS再来看一下SQL和执行计划:

    explain plan for select *
      from t_studentscore ts
     where exists (select 1
              from t_studentinfo
             where mzdm_ = 1
               and bmh_ = ts.bmh_);
    select * from table(dbms_xplan.display());

    观察一下执行计划:

    1 Plan hash value: 3857445149
    2
    3 —————————————————————————————
    4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    5 —————————————————————————————
    6 | 0 | SELECT STATEMENT | | 1 | 71 | 240 (1)| 00:00:03 |
    7 |* 1 | HASH JOIN RIGHT SEMI| | 1 | 71 | 240 (1)| 00:00:03 |
    8 |* 2 | TABLE ACCESS FULL | T_STUDENTINFO | 535 | 5885 | 207 (1)| 00:00:03 |
    9 | 3 | TABLE ACCESS FULL | T_STUDENTSCORE | 11642 | 682K| 32 (0)| 00:00:01 |
    10 —————————————————————————————
    11
    12 Predicate Information (identified by operation id):
    13 —————————————————
    14
    15 1 - access(“TS”.”BMH_”=SYS_OP_C2C(“BMH_”))
    16 2 - filter(“MZDM_”=1)

    如上所示,尽管IN的写法用了HASH JOIN(哈希连接)而EXISTS的写法用了HASH JOIN RIGHT SEMI(哈希右半连接),但它们的执行计划却没有区别,效率都是一样的,这是因为数据量不大,所以有一点结论就是在简单查询中,IN和EXISTS是等价的。还有一点需要明确,在早期的版本中仿佛有这样的规则:

    1. 子查询结果集小,用IN。
    2. 外表小,子查询表大,用EXISTS。

    这两个说法在Oracle11g中已经是完全错误的了!在Oracle8i中这样也许还经常是正确的,但Oracle 9i CBO就已经优化了IN和EXISTS的区别,Oracle优化器有个查询转换器,很多SQL虽然写法不同,但是Oracle优化器会根据既定规则进行查询重写,重写为优化器觉得效率最高的SQL,所以可能SQL写法不同,但是执行计划却是完全一样的,所以还有个结论就是:关于IN和EXISTS哪种更高效应该及时查看PLAN,而不是记固定的结论,至少在目前的Oracle版本中是这样的。

    INNER LEFT RIGHT FULL JOIN

    如题,很常用的几种连接方式,下面就分别看一下它们之间的区别。

    INNER JOIN

    首先是内连接(INNER JOIN),顾名思义,INNER JOIN返回的是两表相匹配的数据,依旧以blog开头的例子改写为INNER JOIN:

    select t1.sid_, t1.stuname_, t1.mzdm_, t2.itemvalue_ mzmc_
      from t_studentinfo t1
     inner join t_dict t2
        on t1.mzdm_ = t2.itemkey_
     where t2.itemname_ = 'EthnicType';
    

    运行结果如下:
    这里写图片描述

    可以看到和上面的结果依旧是完全一样,但这个例子没有说明INNER JOIN的特点,所以就再重新创建两张表说明一下问题,这次用比较经典的学生表班级表来进行测试:

    create table T_TEST_STU
    (
      sid     INTEGER,
      stuname VARCHAR2(20),
      clsid   INTEGER
    )
    tablespace USERS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    create table T_TEST_CLS
    (
      cid   INTEGER,
      cname VARCHAR2(20)
    )
    tablespace USERS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    

    表创建好后插入测试数据:

    insert into T_TEST_STU (SID, STUNAME, CLSID) values (1, '张三', 1);
    
    insert into T_TEST_STU (SID, STUNAME, CLSID) values (2, '李四', 1);
    
    insert into T_TEST_STU (SID, STUNAME, CLSID) values (3, '小明', 2);
    
    insert into T_TEST_STU (SID, STUNAME, CLSID) values (4, '小李', 3);
    
    insert into T_TEST_CLS (CID, CNAME) values (1, '三年级1班');
    
    insert into T_TEST_CLS (CID, CNAME) values (5, '三年级5班');

    如上所示,可以看到非常简单,学生表插入了4条数据,班级表插入了1条数据,用学生表的clsid来关联班级表的cid查询一下班级名称,下面看一下使用INNER JOIN的查询语句:

    select t1.sid, t1.stuname, t2.cname
      from t_test_stu t1
     inner join t_test_cls t2
        on t1.clsid = t2.cid;
    

    运行后可以看到查询结果:
    这里写图片描述

    如上所示,很好的验证了INNER JOIN的概念,即返回两表均匹配的数据,由于班级表只有1条1班的数据和1条5班的数据,而学生表仅有两名1班的学生并且没有任何5班的学生,所以自然只能返回两条。

    LEFT JOIN

    如题,LEFT JOIN是以左表为主表,返回左表的全部数据,右表只返回相匹配的数据,将上面的SQL改为LEFT JOIN看一下:

    select t1.sid, t1.stuname, t2.cname
      from t_test_stu t1
      left join t_test_cls t2
        on t1.clsid = t2.cid;
    

    看一下运行结果:
    这里写图片描述

    如上图所示,也非常简单,因为右表(班级表)并没有2班和3班的数据,所以班级名称不会显示。

    RIGHT JOIN

    如题,RIGHT JOIN和LEFT JOIN是相反的,以右表数据为主表,左表仅返回相匹配的数据,同理将上面的SQL改写为RIGHT JOIN的形式:

    select t1.sid, t1.stuname, t2.cname
      from t_test_stu t1
     right join t_test_cls t2
        on t1.clsid = t2.cid;
    

    运行结果如下:
    这里写图片描述

    如上图,由于是以班级表为主表进行关联,所以匹配到1班的2名学生以及5班的数据。

    FULL JOIN

    如题,顾名思义,FULL JOIN就是不管左右两边是否匹配,一次性显示出所有的查询结果,相当于LEFT JOIN和RIGHT JOIN结果的并集,依旧将上面的SQL改写为FULL JOIN并查看结果:

    select t1.sid, t1.stuname, t2.cname
      from t_test_stu t1
      full join t_test_cls t2
        on t1.clsid = t2.cid;
    

    运行结果如下:
    这里写图片描述

    到这里这4种JOIN查询方式就已经简要的介绍完毕,单从概念上来将还是很好理解和区分的。

    自关联

    如题,这是一个使用场景比较特殊的关联方式,个人感觉如果数据库合理设计的话不会出现这种需求吧,既然提到了就举例说明一下,依旧以上面的测试学生表为例,现在需要添加一个字段:

    alter table T_TEST_STU add leader INTEGER;

    假设有如下需求,每个学生都有一个直属leader,负责检查作业,老师为了避免作弊行为不会指定两个人相互检查,而是依次错开,比如学生A检查学生B,学生B检查学生C,所以我们的表数据可以这样来描述这个问题:
    这里写图片描述

    如上图,张三的LEADER是李四,李四的LEADER是小明,小明的LEADER是小李,而小李的LEADER又是张三,那么问题来了,该如何查询得到每个学生的LEADER的姓名呢?没错,这里就用到了自关联查询,简单的讲就是把同一张表查两遍并进行关联,用视图来说明获取更清晰,所以首先创建两个视图:

    CREATE OR REPLACE VIEW V_STU as select * from T_TEST_STU;
    CREATE OR REPLACE VIEW V_LEADER as select * from T_TEST_STU;

    接下来就通过自关联查询:

    select v1.SID, v1.STUNAME, v1.CLSID, v1.LEADER, v2.STUNAME leader
      from V_STU v1
      left join V_LEADER v2
        on v1.LEADER = v2.SID
     order by v1.SID

    运行结果如下:
    这里写图片描述

    如上图所示,这样就通过自关联很好的查询出了每个学生对应的LEADER的姓名。

    NOT IN和NOT EXISTS

    如题,我们现在有一张学生信息表和一张录取结果表,例如我们想知道有哪些学生没被录取,即学生表有数据但录取表却没有该学生的数据,这时就可以用到NOT IN或NOT EXISTS,依旧结合执行计划看一看这种方式的差异:

    explain plan for
    select * from t_studentinfo where bmh_ not in (select bmh_ from t_lq);
    select * from table(dbms_xplan.display());

    观察一下执行计划:

    1 Plan hash value: 4115710565
    2
    3 —————————————————————————————–
    4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    5 —————————————————————————————–
    6 | 0 | SELECT STATEMENT | | 119 | 52003 | 551 (1)| 00:00:07 |
    7 |* 1 | HASH JOIN RIGHT ANTI NA| | 119 | 52003 | 551 (1)| 00:00:07 |
    8 | 2 | TABLE ACCESS FULL | T_LQ | 11643 | 93144 | 343 (1)| 00:00:05 |
    9 | 3 | TABLE ACCESS FULL | T_STUDENTINFO | 11772 | 4931K| 207 (1)| 00:00:03 |
    10 —————————————————————————————–
    11
    12 Predicate Information (identified by operation id):
    13 —————————————————
    14
    15 1 - access(“BMH_”=”BMH_”)

    接下来将SQL转换为NOT EXISTS再看一下执行计划:

    explain plan for 
    select * from t_studentinfo t1 where not exists (select null from t_lq t2 where t1.bmh_ = t2.bmh_);
    
    select * from table(dbms_xplan.display());

    执行结果如下:

    1 Plan hash value: 270337792
    2
    3 ————————————————————————————–
    4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    5 ————————————————————————————–
    6 | 0 | SELECT STATEMENT | | 119 | 52003 | 551 (1)| 00:00:07 |
    7 |* 1 | HASH JOIN RIGHT ANTI| | 119 | 52003 | 551 (1)| 00:00:07 |
    8 | 2 | TABLE ACCESS FULL | T_LQ | 11643 | 93144 | 343 (1)| 00:00:05 |
    9 | 3 | TABLE ACCESS FULL | T_STUDENTINFO | 11772 | 4931K| 207 (1)| 00:00:03 |
    10 ————————————————————————————–
    11
    12 Predicate Information (identified by operation id):
    13 —————————————————
    14
    15 1 - access(“T1”.”BMH_”=”T2”.”BMH_”)

    如上所示,两个PLAN都应用了HASH JOIN RIGHT ANTI,所以它们的效率是一样的,所以在Oracle11g中关于NOT IN和NOT EXISTS也没有绝对的效率优劣,依旧是要通过PLAN来判断和测试哪种更高效。

    多表查询时的空值处理

    如题,假设有以下需求,我需要查询一下性别不为男的学生的录取分数,但在这之前我首先给学生表添加一条报名号(bmh_)为null的学生数据,如下所示:
    这里写图片描述

    接下来写查询语句,这里刻意用一下NOT IN关键字而不是IN关键字:

    select bmh_, stuname_, lqfs_
      from t_lq
     where bmh_ not in (select bmh_ from t_studentinfo where sextype_ = 1)
    

    运行结果如下图所示:
    这里写图片描述

    我们惊奇的发现没有任何数据被查出来,这就是因为NOT IN后的子查询中的5000+结果中仅仅有一条存在NULL值,所以这个查询整体就不会显示任何结果,有点一只老鼠毁了一锅汤的感觉,这也正是Oracle的特性之一,即:如果NOT IN关键字后的子查询包含空值,则整体查询都会返回空,所以这类查询务必要加非NULL判断条件,即:

    select bmh_, stuname_, lqfs_
      from t_lq
     where bmh_ not in (select bmh_
                          from t_studentinfo
                         where sextype_ = 1
                           and bmh_ is not null);
    

    这次再看一下运行结果:
    这里写图片描述

    如上图所示,这次就很好的查询出了我们需要的结果。

    总结

    简单记录一下Oracle多表查询中的各种模式以及个人认为值得注意的一些点和优化方式,希望对读到的同学有所帮助和提高,The End。

    展开全文
  • Oracle表查询和解锁方法

    万次阅读 多人点赞 2019-05-22 10:07:07
    我们这里一般用的PL/SQL,总是无意间把表锁住,所以我今天就整理了一下简单的解锁和查询的方法; 一、首先PL/SQL要以管理员的账号(system/admin等)登录,管理员的账号和密码根据个人设置而来,连接为一般选择...

    我们这里一般用的PL/SQL,总是无意间把表锁住,所以我今天就整理了一下简单的解锁和查询锁表的方法;

    一、首先PL/SQL要以管理员的账号(system/admin等)登录,管理员的账号和密码根据个人设置而来,连接为一般选择Normal,也可选择SYSDBA;

    二、相关SQL语句:

    --以下几个为相关表
    SELECT*FROMv$lock;
    SELECT*FROMv$sqlarea;
    SELECT*FROMv$session;
    SELECT*FROMv$process;
    SELECT*FROMv$locked_object;
    SELECT*FROMall_objects;
    SELECT*FROMv$session_wait;
    --查看被锁表信息
    SQL > select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode  from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;

    --杀掉锁表进程
    SQL > alter system kill session '68,51';--分别为SID和SERIAL#号

    --查看数据库引起锁表的SQL语句 
    SELECT A.USERNAME,
           A.MACHINE,
           A.PROGRAM,
           A.SID,
           A.SERIAL#,
           A.STATUS,
           C.PIECE,
           C.SQL_TEXT
      FROM V$SESSION A, V$SQLTEXT C
     WHERE A.SID IN (SELECT DISTINCT T2.SID
                       FROM V$LOCKED_OBJECT T1, V$SESSION T2
                      WHERE T1.SESSION_ID = T2.SID)
       AND A.SQL_ADDRESS = C.ADDRESS(+)
     ORDER BY C.PIECE;

    展开全文
  • Oracle表查询

    千次阅读 2018-07-02 18:04:32
    笔者本是个Android开发工程师...联表查询,顾名思义,指的是结合张数据库进行查询,取两张的内容结合成一个视图返回。 在我的项目中,使用的是Spring Boot框架。 下面用一个例子来简单说一下联表查询怎么用...
  • Oracle多表关联查询

    千次阅读 2017-03-12 17:06:38
    在实际的应用系统开发中会涉及多个数据,每个的信息不是独立存在的,而是若干个之间的信息存在一定的关联,这样当用户查询某一个的信息时,很可能需要查询关联的信息...在使用内连接查询多时,必须在FRO
  • oracle 多表联合查询

    千次阅读 2017-04-17 22:38:55
    此时如果要对分组后的数据再次进行过滤,则使用HAVING子句完成,那么此时的SQL语法格式如下: SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计...FROM 名称 [别名], [名称 [别名] ,…
  • Oracle表结构查询

    千次阅读 2019-08-09 09:43:56
    文章目录Oracle表结构查询获取字段获取字段注释查询表所有字段包括注释当前用户的所有用户的包括系统 Oracle表结构查询 以下是查询所的查描述相关的SQL。 获取字段 -- 获取字段 SELECT * FROM user_tab_...
  • oracle 关联查询 查询自身

    千次阅读 2017-02-14 16:34:26
    oracle 关联查询 查询自身
  • Oracle数据库的多表关联查询SQL语句

    千次阅读 2018-10-16 11:41:02
    转至 数据库(学习整理)----7--Oracle多表查询,三种join连接 ======================= 聚合函数:(都会忽略null数据) 常用的有5种:将字段中所有的数据聚合在一条中 1、sum(字段名) :求总和 2、avg...
  • Oracle 多表关联查询后修改

    千次阅读 2014-05-27 10:12:49
    --两张关联查询后修改,修改2014年4月份出租车违法数据的同步标志位 update t_veh_traffic_vio t set synchflag = 0, synchstatus = 'u' where t.wfsj > to_date('2014-04-01', 'yyyy-mm-dd') and t.wfsj ('...
  • Oracle数据库中的多表查询

    万次阅读 多人点赞 2017-10-01 20:25:45
    经验查询: 在日后的开发之中,很多人都肯定要接触到许多新的数据库和数据,那么在这种时候有两种做法; 做法一:新人做法,上来就直接输入以下的命令 select * from 名称; 如果此时数据量较大的话,以上无法...
  • oracle表查询和解操作

    千次阅读 2018-08-07 17:00:22
    数据库操作语句的分类 DDL:数据库模式定义语言,关键字:create ...oracle表在什么情况下会被锁住 DML锁又可以分为,行锁、表锁、死锁 行锁:当事务执行数据库插入、更新、删除操作时,该事务自动获得...
  • ORACLE分区表查询

    万次阅读 2011-07-07 13:28:27
    假如 [分区] 在字段 [createdate] 上进行分区,每个月为一个分区:2009年6月为分区P2009062009年7月为分区P2009072009年8月为分区P200908。。。执行SQL 使用分区键检索:select*from 分区 t where ...
  • Oracle查询表空间

    万次阅读 2015-11-20 16:37:09
     在Oracle中,对于空间的划分,Oracle使用了一种类似于Linux的mount的形式,需要时则分配一块存储空间挂载至某个空间(附:空间即某些可以使用的存储空间大小)上。举个例子:相信大家应该见过大学食堂的...
  • Oracle 分区和普通表查询效率分析对比目录Oracle 分区和普通表查询效率分析对比 目录 测试环境 创建普通的使用toad的generate data生成一千万条记录 创建并以哈希方式分区 创建分区的同时导入海量数据 小...
  • ORACLE中的多表连接查询

    万次阅读 2018-06-07 19:43:35
    这篇文章讲述了多表之间连接,包括内连接、外连接,如有错误或者不妥之处,还请各位大佬批评指正。 连表 SQL中操作多个表,以便可以查询到所需数据,其中包括内连接、外链接、等值连接、非等值连接、左连接、右...
  • Oracle 单个表查询速度极慢处理过程

    万次阅读 2016-03-10 19:13:47
    Oracle 单个表查询速度极慢处理过程   现象: 今天,接到开发人员报有一个表查询了半天也没出结果,表里大概有几十条数据 配置: Oracle 10G RAC 、Aix Unix操作系统 处理过程: 我的第一反应...
  • oracle关联查询

    千次阅读 2018-07-04 17:55:00
    oracle关联查询 CreationTime--2018年7月4日17点52分 Author:Marydon 左连接实现三关联 A---------------------------------关联第一张表B-----------------------关联第二张表c 1.语法  select * ...
  • Oracle表空间查询

    万次阅读 2018-12-07 10:50:26
    --1、查看空间的名称及大小  SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size  FROM dba_tablespaces t, dba_data_files d  WHERE t.tablespace_name = d.tablespace_name  GROUP BY...
  • Oracle基础(多表查询与分页)

    千次阅读 2018-02-06 22:30:48
    多表查询 --1、员工表emp和部门表dept的笛卡尔集(笛卡尔集表=列数之和,行数之积,笛卡尔集表内中有些数据是不符合要求的) select emp.ename,dept.dname from emp,dept; --2、使用等值连接/内连接(只能使用=...
  • Oracle多表查询再按时间倒序

    千次阅读 2010-04-30 10:27:00
    我在工作中在Oracle多表查询再按时间倒序时,用order by 表名.datatime desc 总是实现不了。 不过最后问杨鹃最后和我说可以把我们查询的总个sql语句做为一个表来排序: select rownum,aa.* from (.... order by...
  • SELECT c.typeName siteName, NVL (SUM(o.recommCount), 0) recommCount, NVL (SUM(o.acceptCount), 0) acceptCount FROM w1 o RIGHT JOIN w2 n ON o.info_id = n. ID AND n.PUBYEAR='2019...RIGHT JOIN w3 c ON...
  • select t.tid,t.*,z.*,f.* from 1 t,2z, (select * from (select rank() over(partition by bd_id order by p_id desc) r,a.* from 3 a) where r=1) f where t.tid=z.zid and 1=1 and t.tid=f.fid
  • Oracle查询表里重复数据

    万次阅读 2018-12-21 11:01:15
    一、查询结果只显示重复的字段 1. 查询重复的单个字段(group by) ...2.查询重复的个字段(group by) select 重复字段A, 重复字段B, count(*) from group by 重复字段A, 重复字段B h...
  • SELECT * FROM 1 t1 JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY 单据ID ORDER BY 处理时间 DESC) rn FROM 2 ) t2 ON t1.单据ID = t2.单据ID AND t2.rn = 1 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 524,274
精华内容 209,709
关键字:

orcle多表查询