SQL 删除重复数据|极客教程

SQL 删除重复数据，你正在生成一个报表，当相邻两行的某列出现了相同值时，你希望那个值只显示一次。例如，你想从 EMP 表中提取出 DEPTNO 和 ENAME，希望按照 DEPTNO 对所有的行进行分组，并且希望每个 DEPTNO 只显示一次。你希望返回如下所示的结果集。

SQL 删除重复数据问题描述

你正在生成一个报表，当相邻两行的某列出现了相同值时，你希望那个值只显示一次。例如，你想从 EMP 表中提取出 DEPTNO 和 ENAME，希望按照 DEPTNO 对所有的行进行分组，并且希望每个 DEPTNO 只显示一次。你希望返回如下所示的结果集。

DEPTNO ENAME
------ ---------
    10 CLARK
       KING
       MILLER
    20 SMITH
       ADAMS
       FORD
       SCOTT
       JONES
    30 ALLEN
       BLAKE
       MARTIN
       JAMES
       TURNER
       WARD

SQL 删除重复数据解决方案

这是一个很简单的格式化问题，Oracle 提供的窗口函数 LAG OVER 能很容易地解决这一问题。当然，使用标量子查询和其他窗口函数也能达到同样目的（对于非 Oracle 用户只能如此），但这里最方便的做法是使用 LAG OVER 函数。
DB2 和 SQL Server
使用窗口函数 MIN OVER 为每个 DEPTNO 找出最小的 EMPNO，然后使用 CASE 表达式来“涂改”那些 EMPNO 不等于该最小值的行。

 1  select case when empno=min_empno
 2              then deptno else null
 3         end deptno,
 4         ename
 5    from (
 6  select deptno,
 7         min(empno)over(partition by deptno) min_empno,
 8         empno,
 9         ename
10    from emp
11         ) x

Oracle
使用窗口函数 LAG OVER 访问当前行的前一行，为每个分区间找出第一个 DEPTNO。

1 select to_number(
2           decode(lag(deptno)over(order by deptno),
3                 deptno,null,deptno)
4        ) deptno, ename
5   from emp

PostgreSQL 和 MySQL
本实例着重介绍如何使用窗口函数方便地访问到当前行前面和后面的行数据。在写作本书时，这些数据库尚未支持这类窗口函数。

SQL 删除重复数据扩展知识

DB2 和 SQL Server
首先，使用窗口函数 MIN OVER 找出每个 DEPTNO 对应的最小 EMPNO 值。

select deptno,
       min(empno)over(partition by deptno) min_empno,
       empno,
       ename
  from emp
　
DEPTNO MIN_EMPNO       EMPNO ENAME
------ ---------- ---------- ----------
    10       7782       7782 CLARK
    10       7782       7839 KING
    10       7782       7934 MILLER
    20       7369       7369 SMITH
    20       7369       7876 ADAMS
    20       7369       7902 FORD
    20       7369       7788 SCOTT
    20       7369       7566 JONES
    30       7499       7499 ALLEN
    30       7499       7698 BLAKE
    30       7499       7654 MARTIN
    30       7499       7900 JAMES
    30       7499       7844 TURNER
    30       7499       7521 WARD

下一步也是最后一步，使用 CASE 表达式删除重复的 DEPTNO。如果一个员工的 EMPNO 和 MIN_EMPNO 相等，则返回 DEPTNO，否则返回 Null。

select case when empno=min_empno
            then deptno else null
       end deptno,
       ename
  from (
select deptno,
       min(empno)over(partition by deptno) min_empno,
       empno,
       ename
  from emp
       ) x
　
DEPTNO ENAME
------ ----------
    10 CLARK
       KING
       MILLER
    20 SMITH
       ADAMS
       FORD
       SCOTT
       JONES
    30 ALLEN
       BLAKE
       MARTIN
       JAMES
       TURNER
       WARD

Oracle
首先，使用窗口函数 LAG OVER 为每一行返回前一行的 DEPTNO。

select lag(deptno)over(order by deptno) lag_deptno,
       deptno,
       ename
  from emp
　
LAG_DEPTNO     DEPTNO ENAME
---------- ---------- ----------
                   10 CLARK
        10         10 KING
        10         10 MILLER
        10         20 SMITH
        20         20 ADAMS
        20         20 FORD
        20         20 SCOTT
        20         20 JONES
        20         30 ALLEN
        30         30 BLAKE
        30         30 MARTIN
        30         30 JAMES
        30         30 TURNER
        30         30 WARD

如果仔细观察以上结果集的话，很容易区分出哪些行的 DEPTNO 和 LAG_DEPTNO 相等。对于这些行，我们希望把 DEPTNO 显示为 Null。我们可以借助 DECODE 函数做到这一点（TO_NUMBER 函数可以把 DEPTNO 转换为数字）。

select to_number(
           decode(lag(deptno)over(order by deptno),
                  deptno,null,deptno)
       ) deptno, ename
  from emp
　
DEPTNO ENAME
------ ----------
    10 CLARK
       KING
       MILLER
    20 SMITH
       ADAMS
       FORD
       SCOTT
       JONES
    30 ALLEN
       BLAKE
       MARTIN
       JAMES
       TURNER
       WARD