系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事

【SQL开发实战技巧】系列（二）：简单单表查询

【SQL开发实战技巧】系列（三）：SQL排序的那些事

【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项

【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论

【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放

【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起

【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表

【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！

【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起

【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数

【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)

【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加

【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工

【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report

【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算

【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数

【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算

【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？

【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据

【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据

【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式

【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高

【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的

【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组

【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

• 系列文章目录
• 前言
• 一、如果有重复数据如何检查出两个表中的差异数据及对应条数
• 二、表连接做聚合容易出现重复计算的错误
• 三、多表查询空值处理问题
• 四、NOT IN的子查询范围不能是空值，否则查询结果为空
• 总结

  ---

  前言

  本篇文章讲解的主要内容是：如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空。

  【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

  ---

  一、如果有重复数据如何检查出两个表中的差异数据及对应条数

  有这么一个临时表

  with t as (
  SELECT* FROM emp WHERE deptno != 10
  UNION ALL
  SELECT* FROM emp WHERE ename = 'SCOTT'
  )
  select * from t
  

  要求用查询找出T与表emp中不同的数据。

  注意：T中员工SCOTT有两行数据，而emp表中只有一条数据。

  SQL> with t as (
    2  SELECT* FROM emp WHERE deptno != 10
    3  UNION ALL
    4  SELECT* FROM emp WHERE ename = 'SCOTT'
    5  )
    6  select rownum,empno,ename from t where ename='SCOTT';
      ROWNUM EMPNO ENAME
  ---------- ----- ----------
           1  7788 SCOTT
           2  7788 SCOTT
  SQL> select rownum,empno,ename from emp where ename='SCOTT';
      ROWNUM EMPNO ENAME
  ---------- ----- ----------
           1  7788 SCOTT
  

  比较两个数据集的不同时，通常用类似下面的FULL JOIN语句:

  with t as
   (SELECT *
      FROM emp
     WHERE deptno != 10
    UNION ALL
    SELECT *
      FROM emp
     WHERE ename = 'SCOTT')
  select t.empno, t.ename, e.empno, e.ename
    from t
    full join emp e
      on (t.empno = e.empno)
   where t.empno is null
      or e.empno is null;
  EMPNO ENAME      EMPNO ENAME
  ----- ---------- ----- ----------
                    7782 CLARK
                    7839 KING
                    7934 MILLER
                    1001 test
  

  但是这种语句在这个案例中查不到SCOTT的区别。那我们应该怎么才能查到呢？

  我们可以先对数据进行处理，增加一列显示相同数据的条数，再进行比较：

  with t as
   (SELECT *
      FROM emp
     WHERE deptno != 10
    UNION ALL
    SELECT *
      FROM emp
     WHERE ename = 'SCOTT')
  select t.empno, t.ename, e.empno, e.ename
    from (select empno, ename, count(*) cnt from t group by empno, ename) t
    full join (select empno, ename, count(*) cnt
                 from emp
                group by empno, ename) e
      on (t.empno = e.empno and t.cnt = e.cnt)
   where t.empno is null
      or e.empno is null;
  EMPNO ENAME      EMPNO ENAME
  ----- ---------- ----- ----------
                    1001 test
                    7782 CLARK
                    7788 SCOTT
                    7839 KING
                    7934 MILLER
   7788 SCOTT            
  6 rows selected
  

  二、表连接做聚合容易出现重复计算的错误

  现在有一张员工级别临时表：

  with t as (
  select '7934' as empno,1 as lev from dual 
  union all
  select '7934' as empno,2 as lev from dual 
  union all
  select '7839' as empno,3 as lev from dual 
  union all
  select '7782' as empno,1 as lev from dual 
  )
  

  员工的奖金根据lev计算，lev=1的奖金为员工工资的10%,lev=2的奖金为员工工资的20%,lev=3的奖金为员工工资的30%。

  现要求返回上述员工（也就是部门10的所有员工）的工资及奖金。

  如果你马上想到的是先关联，然后对结果做聚集。那么你可以尝试一下，会发现7934’的工资被重复计算了两次，正确的做法应该是先对T表做聚合然后再关联！正确的SQL放到下面！

  with t as (
  select '7934' as empno,1 as lev from dual 
  union all
  select '7934' as empno,2 as lev from dual 
  union all
  select '7839' as empno,3 as lev from dual 
  union all
  select '7782' as empno,1 as lev from dual 
  ),
  t1 as (
  select empno,
         sum(case when lev = 1 then 0.1 when lev = 2 then 0.2 when lev = 3 then
             0.3end) as levs
    from t
   group by empno
  )
  select deptno, sum(sal) as sumsal, sum(sal * t1.levs) as sumlevs
    from emp
   inner join t1
      on (emp.empno = t1.empno)
   where deptno = 10
   group by deptno
  

  三、多表查询空值处理问题

  先看下当前emp表数据：

  SQL> select * from emp
    2  ;
  EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
  ----- ---------- --------- ----- ----------- --------- --------- ------
   7369 SMITH      CLERK      7902 1980-12-17     800.00               20
   7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
   7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
   7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
   7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
   7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
   7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
   7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
   7839 KING       PRESIDENT       1981-11-17    5000.00               10
   7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
   7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
   7900 JAMES      CLERK      7698 1981-12-3      950.00               30
   7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
   7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
   1001 test                       2021-10-9 1                     
  15 rows selected
  

  接下来我有一个需求：让我们查找comm小于1400的员工信息：

  SQL> select * from emp where comm < 1400;
  EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
  ----- ---------- --------- ----- ----------- --------- --------- ------
   7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
   7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
   7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
  

  不对呀，哪些comm是null的员工也得查出来啊，那么我们在查询的时候要注意，如果你不能保证你要比较大小的列(比如comm)，那你写sql的时候要注意，对空值一定要做个转换，正确的写法：

  SQL> select * from emp where coalesce(comm,0) < 1400;
  EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
  ----- ---------- --------- ----- ----------- --------- --------- ------
   7369 SMITH      CLERK      7902 1980-12-17     800.00               20
   7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
   7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
   7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
   7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
   7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
   7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
   7839 KING       PRESIDENT       1981-11-17    5000.00               10
   7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
   7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
   7900 JAMES      CLERK      7698 1981-12-3      950.00               30
   7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
   7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
   1001 test                       2021-10-9 1                     
  14 rows selected
  

  上面我还是使用的coalesce，你也可以用nvl，前面文章讨论过这俩的区别了。

  四、NOT IN的子查询范围不能是空值，否则查询结果为空

  先看一个查询：

  SQL> SELECT* FROM dept WHERE deptno NOT IN (SELECT emp.deptno FROM emp WHERE emp.deptno  IS NOT NULL);
  DEPTNO DNAME          LOC
  ------ -------------- -------------
      40 OPERATIONS     BOSTON
  

  如果我们把这个WHERE emp.deptno IS NOT NULL去掉呢？

  SQL> SELECT* FROM dept WHERE deptno NOT IN (SELECT emp.deptno FROM emp);
  DEPTNO DNAME          LOC
  ------ -------------- -------------
  SQL> 
  

  发现没有查询结果了，这是因为啥？

  我们之前文章介绍过，NULL不支持加、减、乘、除、大小比较、相等比较，否则只能为空。

  所以这里类比成SELECT* FROM dept WHERE deptno NOT IN (null)，那结果肯定是空值了，这里一定要记住，如果你不能保证你的not in子查询范围一定不为空，那一定要加上null值过滤条件，否则你的查询结果是错误的！

  ---

  总结

  本章介绍的如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空这四个案例，是工作中非常容易遇到的场景，也是很容易犯错的地方，博主写出来也给自己一个提醒！