当前位置：Gxlcms > 数据库问题 > Oracle SQL1-子查询改为表连接

Oracle SQL1-子查询改为表连接

时间：2021-07-01 10:21:17 帮助过：14人阅读

table emp (empno varchar2(10),ename varchar2(10),sal varchar2(10),deptno varchar2(10)); commit; insert into EMP (EMPNO, ENAME, SAL, DEPTNO) values (‘E01‘, ‘NE01‘, ‘1000‘, ‘D01‘); insert into EMP (EMPNO, ENAME, SAL, DEPTNO) values (‘E02‘, ‘NE02‘, ‘2000‘, ‘D01‘); insert into EMP (EMPNO, ENAME, SAL, DEPTNO) values (‘E03‘, ‘NE03‘, ‘3000‘, ‘D01‘); insert into EMP (EMPNO, ENAME, SAL, DEPTNO) values (‘E04‘, ‘NE04‘, ‘4000‘, ‘D01‘); insert into EMP (EMPNO, ENAME, SAL, DEPTNO) values (‘E05‘, ‘NE05‘, ‘5000‘, ‘D02‘); insert into EMP (EMPNO, ENAME, SAL, DEPTNO) values (‘E06‘, ‘NE06‘, ‘6000‘, ‘D02‘); commit; create table dept (deptno varchar2(10),dname varchar2(10)); commit; insert into DEPT (DEPTNO, DNAME) values (‘D01‘, ‘ND01‘); insert into DEPT (DEPTNO, DNAME) values (‘D02‘, ‘ND02‘); insert into DEPT (DEPTNO, DNAME) values (‘D04‘, ‘ND03‘); insert into DEPT (DEPTNO, DNAME) values (‘D04‘, ‘ND04‘); commit; 数据脚本1 
/*最简单的的子查询改为左连接。*/
SELECT E.EMPNO,
       E.ENAME,
       E.SAL,
       (SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
  FROM EMP E;

SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME
  FROM EMP E
  LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

/*带聚合的子查询改为左连接。*/
SELECT D.DEPTNO,
       D.DNAME,
       NVL((SELECT SUM(E.SAL) FROM EMP E WHERE E.DEPTNO = D.DEPTNO), 0) AS SUM_SAL
  FROM DEPT D;

SELECT D.DEPTNO, D.DNAME, NVL(SUM(E.SAL), 0) AS SUM_SAL
  FROM DEPT D
  LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
 GROUP BY D.DEPTNO, D.DNAME;/*此处的Group By处理相对下面的处理，相对低效率*/

SELECT D.DEPTNO, D.DNAME, NVL(EE.SUM_SAL, 0) AS SUM_SAL
  FROM DEPT D
  LEFT JOIN (SELECT E.DEPTNO, SUM(E.SAL) AS SUM_SAL
               FROM EMP E
              GROUP BY E.DEPTNO) EE ON D.DEPTNO = EE.DEPTNO; /*此处先对需要关联的表进行一次聚合，在进行做链接，感觉性能开销会更小*/
技术分享 
create table dept (deptno varchar2(10),dname varchar2(10));
commit;
insert into DEPT (DEPTNO, DNAME)
values (‘D01‘, ‘ND01‘);
insert into DEPT (DEPTNO, DNAME)
values (‘D02‘, ‘ND02‘);
insert into DEPT (DEPTNO, DNAME)
values (‘D04‘, ‘ND03‘);
insert into DEPT (DEPTNO, DNAME)
values (‘D04‘, ‘ND04‘);
commit;
create table EMP
(
  EMPNO    VARCHAR2(10),
  ENAME    VARCHAR2(10),
  SAL      VARCHAR2(10),
  DEPTNO   VARCHAR2(10),
  SAL_DATE VARCHAR2(10)
)
commit;
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E01‘, ‘NE01‘, ‘1000‘, ‘D01‘, ‘01‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E02‘, ‘NE02‘, ‘2000‘, ‘D01‘, ‘01‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E03‘, ‘NE03‘, ‘3000‘, ‘D01‘, ‘01‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E04‘, ‘NE04‘, ‘4000‘, ‘D01‘, ‘01‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E05‘, ‘NE05‘, ‘5000‘, ‘D02‘, ‘01‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E06‘, ‘NE06‘, ‘6000‘, ‘D02‘, ‘01‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E01‘, ‘NE01‘, ‘2000‘, ‘D01‘, ‘02‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E02‘, ‘NE02‘, ‘2000‘, ‘D01‘, ‘02‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E03‘, ‘NE03‘, ‘3000‘, ‘D01‘, ‘02‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E04‘, ‘NE04‘, ‘4000‘, ‘D01‘, ‘02‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E05‘, ‘NE05‘, ‘5000‘, ‘D02‘, ‘02‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E06‘, ‘NE06‘, ‘6000‘, ‘D02‘, ‘02‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E01‘, ‘NE01‘, ‘1000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E02‘, ‘NE02‘, ‘4000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E03‘, ‘NE03‘, ‘3000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E04‘, ‘NE04‘, ‘4000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E05‘, ‘NE05‘, ‘5000‘, ‘D02‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E06‘, ‘NE06‘, ‘6000‘, ‘D02‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E01‘, ‘NE01‘, ‘1000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E02‘, ‘NE02‘, ‘2000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E03‘, ‘NE03‘, ‘3000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E04‘, ‘NE04‘, ‘4000‘, ‘D01‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E05‘, ‘NE05‘, ‘5000‘, ‘D02‘, ‘03‘);
insert into EMP (EMPNO, ENAME, SAL, DEPTNO, SAL_DATE)
values (‘E06‘, ‘NE06‘, ‘6000‘, ‘D02‘, ‘03‘);
commit;
数据脚本2 
SELECT D.DEPTNO,
       D.DNAME,
       (SELECT SUM(E.SAL)
          FROM EMP E
         WHERE E.SAL_DATE = ‘01‘
           AND E.DEPTNO = D.DEPTNO) AS SUM_SAL_01,
       (SELECT SUM(E.SAL)
          FROM EMP E
         WHERE E.SAL_DATE = ‘02‘
           AND E.DEPTNO = D.DEPTNO) AS SUM_SAL_02,
       (SELECT SUM(E.SAL)
          FROM EMP E
         WHERE E.SAL_DATE = ‘03‘
           AND E.DEPTNO = D.DEPTNO) AS SUM_SAL_03
  FROM DEPT D;

SELECT D.DEPTNO,D.DNAME,EE.SUM_SAL_01,EE.SUM_SAL_02,EE.SUM_SAL_03 FROM DEPT D
LEFT JOIN 
(SELECT E.DEPTNO,
       SUM(CASE WHEN E.SAL_DATE = ‘01‘ THEN TO_NUMBER(E.SAL) ELSE 0 END) AS SUM_SAL_01,
       SUM(CASE WHEN E.SAL_DATE = ‘02‘ THEN TO_NUMBER(E.SAL) ELSE 0 END) AS SUM_SAL_02,
       SUM(CASE WHEN E.SAL_DATE = ‘03‘ THEN TO_NUMBER(E.SAL) ELSE 0 END) AS SUM_SAL_03
  FROM EMP E GROUP BY E.DEPTNO) EE
  ON D.DEPTNO=EE.DEPTNO;

  不等连接的子查询改为连接查询

技术分享 
create table SALDATE
(
  ITEM_NO   VARCHAR2(10),
  SALE_DATE VARCHAR2(10)
);
commit;
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘001‘, ‘01‘);
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘001‘, ‘05‘);
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘001‘, ‘06‘);
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘002‘, ‘02‘);
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘002‘, ‘10‘);
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘003‘, ‘01‘);
insert into SALDATE (ITEM_NO, SALE_DATE)
values (‘003‘, ‘02‘);
commit;

create table PRODUCT
(
  ITEM_NO     VARCHAR2(10),
  ITEM_NM     VARCHAR2(10),
  PRO_ADDRESS VARCHAR2(10),
  PRO_DATE    VARCHAR2(10)
);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘001‘, ‘A001‘, ‘TOKYO‘, ‘04‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘002‘, ‘A002‘, ‘TOKYO‘, ‘04‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘003‘, ‘A003‘, ‘TOKYO‘, ‘07‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘004‘, ‘A004‘, ‘BEIJING‘, ‘08‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘005‘, ‘A005‘, ‘BEIJING‘, ‘07‘);
commit;
数据脚本3 
SELECT P.ITEM_NO,
       P.ITEM_NM,
       P.PRO_DATE,
       (SELECT MIN(S.SALE_DATE)
          FROM SALDATE S
         WHERE S.SALE_DATE >= P.PRO_DATE
           AND P.ITEM_NO = S.ITEM_NO) AS MIN_SALDATE
  FROM PRODUCT P
 ORDER BY P.ITEM_NO, P.ITEM_NM, P.PRO_DATE, MIN_SALDATE;
  
/*构造临时表，筛选数据后用ROWID进行关联*/
  WITH TMP_TBL AS
  (SELECT P.ROWID AS RID,
         MIN(CASE
               WHEN S.SALE_DATE >= P.PRO_DATE THEN
                S.SALE_DATE
             END) AS MIN_SALDATE
    FROM PRODUCT P
   LEFT JOIN SALDATE S ON P.ITEM_NO = S.ITEM_NO
   GROUP BY P.ROWID)
   SELECT P.ITEM_NO, P.ITEM_NM, P.PRO_DATE, T.MIN_SALDATE
     FROM PRODUCT P
     LEFT JOIN TMP_TBL T ON P.ROWID = T.RID
    ORDER BY P.ITEM_NO, P.ITEM_NM, P.PRO_DATE, T.MIN_SALDATE;
 
/*下面这种不是等价改写，例如Product表中有重复数据时，检索结果可能不一样*/
 SELECT P.ITEM_NO, P.ITEM_NM, P.PRO_DATE, MIN(S.SALE_DATE) AS MIN_SALDATE
   FROM PRODUCT P
   LEFT JOIN SALDATE S ON P.ITEM_NO = S.ITEM_NO
                      AND S.SALE_DATE >= P.PRO_DATE
  GROUP BY P.ITEM_NO, P.ITEM_NM, P.PRO_DATE
  ORDER BY P.ITEM_NO, P.ITEM_NM, P.PRO_DATE, MIN_SALDATE;

  主表带检索条件的不等连接的子查询改为连接查询

技术分享 
create table PRODUCT
(
  ITEM_NO     VARCHAR2(10),
  ITEM_NM     VARCHAR2(10),
  PRO_ADDRESS VARCHAR2(10),
  PRO_DATE    VARCHAR2(10)
);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘001‘, ‘A001‘, ‘TOKYO‘, ‘04‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘002‘, ‘A002‘, ‘TOKYO‘, ‘04‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘003‘, ‘A003‘, ‘TOKYO‘, ‘07‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘004‘, ‘A004‘, ‘BEIJING‘, ‘08‘);
insert into PRODUCT (ITEM_NO, ITEM_NM, PRO_ADDRESS, PRO_DATE)
values (‘005‘, ‘A005‘, ‘BEIJING‘, ‘07‘);
commit;
create table SALDATE
(
  ITEM_NO   VARCHAR2(10),
  SALE_DATE VARCHAR2(10),
  BUY_DATE  VARCHAR2(10)
)
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘001‘, ‘01‘, ‘01‘);
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘001‘, ‘05‘, ‘09‘);
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘001‘, ‘06‘, ‘07‘);
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘002‘, ‘02‘, ‘12‘);
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘002‘, ‘10‘, ‘10‘);
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘003‘, ‘01‘, ‘13‘);
insert into SALDATE (ITEM_NO, SALE_DATE, BUY_DATE)
values (‘003‘, ‘02‘, ‘02‘);
commit;
数据脚本4 
SELECT P.ITEM_NO,
       P.ITEM_NM,
       P.PRO_DATE,
       (SELECT MIN(S.SALE_DATE)
          FROM SALDATE S
         WHERE S.ITEM_NO = P.ITEM_NO
           AND S.SALE_DATE >= P.PRO_DATE) AS MIN_SALDATE,
       (SELECT MIN(S.BUY_DATE)
          FROM SALDATE S
         WHERE S.ITEM_NO = P.ITEM_NO
           AND S.BUY_DATE >= P.PRO_DATE) AS MIN_SALDATE
  FROM PRODUCT P
 WHERE P.ITEM_NO=‘001‘;

WITH TMP_TBL AS
  (SELECT P.ROWID AS RID,
         MIN(CASE
               WHEN S.SALE_DATE >= P.PRO_DATE THEN
                S.SALE_DATE
             END) AS MIN_SALDATE,
         MIN(CASE
               WHEN S.BUY_DATE >= P.PRO_DATE THEN
                S.BUY_DATE
             END) AS MIN_BUYDATE
    FROM PRODUCT P
    LEFT JOIN SALDATE S ON P.ITEM_NO = S.ITEM_NO
   GROUP BY P.ROWID)
  SELECT P.ITEM_N

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！