ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE 用EXISTS替換DISTINCT

ORACLE 用EXISTS替換DISTINCT

原创 Linux操作系统 作者:jack198409 时间:2008-04-18 15:16:40 0 删除 编辑

             当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXISTS替换
現測試數據如下:

         SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
  2             UNION
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
  4             UNION
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUAL
  6             UNION
  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
  8             ),
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
 10             UNION
 11             select 'A' DEPTNO,2 ID FROM DUAL
 12             UNION
 13             select 'B' DEPTNO,1 ID FROM DUAL
 14             UNION
 15             select 'B' DEPTNO,3 ID FROM DUAL
 16             UNION
 17             select 'C' DEPTNO,4 ID FROM DUAL
 18             UNION
 19             select 'E' DEPTNO,5 ID FROM DUAL
 20             )
 21  select  DISTINCT A.DEPTNO,A.NAME FROM A,B
 22  WHERE A.DEPTNO=B.DEPTNO
 23  ORDER BY A.DEPTNO
 24  /
 
DEPTNO NAME
------ ----
A      A1
B      B1
C      C1

 

不加DISTINCT結果如下

SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
  2             UNION
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
  4             UNION
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUAL
  6             UNION
  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
  8             ),
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
 10             UNION
 11             select 'A' DEPTNO,2 ID FROM DUAL
 12             UNION
 13             select 'B' DEPTNO,1 ID FROM DUAL
 14             UNION
 15             select 'B' DEPTNO,3 ID FROM DUAL
 16             UNION
 17             select 'C' DEPTNO,4 ID FROM DUAL
 18             UNION
 19             select 'E' DEPTNO,5 ID FROM DUAL
 20             )
 21  select A.DEPTNO,A.NAME FROM A,B
 22  WHERE  A.DEPTNO=B.DEPTNO
 23  /
 
DEPTNO NAME
------ ----
A      A1
A      A1
B      B1
B      B1
C      C1

用EXISTS來替換DISTINCT

 SQL> WITH A AS (SELECT 'A' DEPTNO,'A1' NAME FROM DUAL
  2             UNION
  3             SELECT 'B' DEPTNO,'B1' NAME FROM DUAL
  4             UNION
  5             SELECT 'C' DEPTNO,'C1' NAME FROM DUAL
  6             UNION
  7             SELECT 'D' DEPTNO,'D1' NAME FROM DUAL
  8             ),
  9       B AS (select 'A' DEPTNO,1 ID FROM DUAL
 10             UNION
 11             select 'A' DEPTNO,2 ID FROM DUAL
 12             UNION
 13             select 'B' DEPTNO,1 ID FROM DUAL
 14             UNION
 15             select 'B' DEPTNO,3 ID FROM DUAL
 16             UNION
 17             select 'C' DEPTNO,4 ID FROM DUAL
 18             UNION
 19             select 'E' DEPTNO,5 ID FROM DUAL
 20             )
 21  select A.DEPTNO,A.NAME FROM A
 22  WHERE EXISTS (SELECT 1 FROM B WHERE A.DEPTNO=B.DEPTNO)
 23  /
 
DEPTNO NAME
------ ----
A      A1
B      B1
C      C1

 

結論如下:用EXISTS的時候,可以自動去掉重復的紀錄,需要申明一點的是一定要是一對一(或者一對多)的表信息才可以,如果是兩表的紀錄是多對一的關系的話就有問題!

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    283106