qq260611363
qq260711363
采纳率0%
2018-12-25 11:02 阅读 985

请大神帮忙SQL改写,not exists 改写成 外连接

这个是Oracle的示例表,emp,dept。分别是14条和4条记录,c表2条。14x4x2=112条记录,这个c表中的manager和7839在emp表中是存在的,所以减去存在的3条记录,最后是109条记录。

目的是通过笛卡尔积补充数据。

其实我在做优化,只是不知道这个怎么改,真的好难啊。
a表1.5亿数据,b表13,c表2。这是40多亿的数据,然后在从中拿不存在的,

SELECT a.empno, a.deptno, a.ename, a.sal, c.job, c.mgr, b.deptno
  FROM (SELECT DISTINCT empno, deptno, ename, sal FROM emp) a,
       dept b,
       (SELECT 'MANAGER' AS job, '9999' AS mgr
          FROM dual
        UNION ALL
        SELECT 'MANAGER' AS job, '7839' AS mgr
          FROM dual) c
 WHERE NOT EXISTS (SELECT 1
          FROM emp t
         WHERE t.empno = a.empno
           AND t.deptno = a.deptno
           AND t.ename = a.ename
           AND t.sal = a.sal
           AND t.job = c.job
           AND t.mgr = c.mgr
           AND t.deptno = b.deptno);

怎么改写成外连接,保证数据量一样

新人没有币,见谅。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • u013300742 孤独不是寂寞 2018-12-25 03:27

    我sql水平也一般,不知道写的对不对,没法测试,主要不清楚表结构和你sql语句的目的。
    SELECT
    a.empno,
    a.deptno,
    a.ename,
    a.sal,
    c.job,
    c.mgr,
    b.deptno
    FROM
    emp t
    INNER JOIN (SELECT DISTINCT empno,deptno,ename,sal FROM emp) a ON t.empno != a.empno AND t.deptno != a.deptno AND t.ename != a.ename AND t.sal != a.sal
    INNER JOIN dept b ON t.deptno != b.deptno
    INNER JOIN (SELECT 'MANAGER' AS job,'9999' AS mgr FROM dual UNION ALL SELECT 'MANAGER' AS job,'7839' AS mgr FROM dual) c ON t.job != c.job AND t.mgr != c.mgr

    点赞 评论 复制链接分享
  • qq_37129624 ghq-yes 2018-12-25 04:42

    你直接把not exist去掉,然后后面一大串条件改成不等于不就行了吗

    点赞 评论 复制链接分享
  • qq_40090700 qq_40090700 2018-12-29 07:23

    我现在习惯性会写left join ,我觉得你可以根据查询的先后顺序使用左关内关,可以的,然后再最后加上筛选条件会好点;
    (SELECT 'MANAGER' AS job, '9999' AS mgr
    FROM dual
    UNION ALL
    SELECT 'MANAGER' AS job, '7839' AS mgr
    FROM dual) c
    这一段我有点没有看懂,如果有dual这个表,你可以再语句中写case,这样再表中计算;
    SELECT DISTINCT a.empno, a.deptno, a.ename, a.sal, c.job, c.mgr, b.deptno
    FROM emp a left join dept b on a.条件=b.条件
    再加入C表,可以试一下

    点赞 评论 复制链接分享
  • u010932643 ORA0001 2019-09-02 17:52

    SELECT a.empno, a.deptno, a.ename, a.sal, c.job, c.mgr, b.deptno
    FROM (SELECT DISTINCT empno, deptno, ename, sal FROM emp) a
    inner join dept b on 1=1
    inner join (SELECT 'MANAGER' AS job, '9999' AS mgr
    FROM dual
    UNION ALL
    SELECT 'MANAGER' AS job, '7839' AS mgr
    FROM dual) c on 1=1
    left join emp t on t.empno = a.empno
    AND t.deptno = a.deptno
    AND t.ename = a.ename
    AND t.sal = a.sal
    AND t.job = c.job
    AND t.mgr = c.mgr
    AND t.deptno = b.deptno
    where t.deptno is null;

    点赞 评论 复制链接分享

相关推荐