SQL查询重复数据和清除重复数据

选择重复，消除重复和选择出序列 

有例表：emp 

emp_no   name    age     

    001            Tom      17      

    002            Sun       14      

    003            Tom       15      

    004            Tom      16  

要求： 

列出所有名字重复的人的记录 

( 1 )最直观的思路：要知道所有名字有重复人资料，首先必须知道哪个名字重复了： 

select    name    from    emp        group     by    name      having     count ( * ) > 1  


 所有名字重复人的记录是: 

select     *     from    emp 

    where  name    in    ( select    name    from    emp  group     by    name  having   count ( * ) > 1 ) 

( 2 )稍微再聪明一点，就会想到，如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有 

select     *     from    emp    where    ( select     count ( * )    from    emp   e     where    e.name = emp.name)    > 1  

-- 注意一下这个>1，想下如果是 =1，如果是 =2 如果是>2 如果 e 是另外一张表 而且是=0那结果 就更好玩了:) 

这个过程是 在判断工号为001的 人 的时候先取得 001的 名字（emp.name） 然后和原表的名字进行比较 e.name 

注意e是emp的一个别名。 

再稍微想得多一点，就会想到，如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求： 

select     *     from    emp     

     where     exists      

                  ( select     *     from    emp   e     where    e.name = emp.name    and    e.emp_no <> emp.emp_no) 

 此思路的join写法： 

select    emp. *         from    emp,emp e

        where emp.name = e.name  and  emp.emp_no <> e.emp_no /**/

/*     这个语句较规范的   join   写法是     

select emp.* from   emp   inner join emp   e     on emp.name=e.name and emp.emp_no<>e.emp_no     

但个人比较倾向于前一种写法，关键是更清晰     */      

b、有例表： emp     

name     age     

Tom        16      

Sun        14      

Tom       16      

Tom       16  

-- --------------------------------------------------清除重复----------------------------------------------------
过滤掉所有多余的重复记录 
( 1 )我们知道distinct、 group   by  可以过滤重复，于是就有最直观的 
 
select     distinct     *     from    emp     或      select    name,age    from    emp    group     by    name,age 

获得需要的数据，如果可以使用临时表就有解法： 
 
select     distinct     *     into    #tmp     from    emp   

    delete     from    emp   

    insert     into    emp    select     *     from    #tmp 

( 2 )但是如果不可以使用临时表，那该怎么办？ 
我们观察到我们没办法区分数据（物理位置不一样，对 SQL Server来说没有任何区别），思路自然是想办法把数据区分出来了，既然现在的所有的列都没办法区分数据，唯一的办法就是再加个列让它区分出来，加什么列好？最佳选择是identity列： 
 
alter     table    emp    add    chk    int     identity ( 1 , 1 ) 

 表示例： 
 
name   age   chk     

    Tom     16      1      

    Sun      14      2      

    Tom     16      3      

    Tom     16      4  

重复记录可以表示为： 
 
select     *     from    emp  where  ( select     count ( * )    from    emp   e    where    e.name = emp.name) > 1  

 要删除的是： 
 
delete     from    emp 

    where  ( select     count ( * )    from    emp   e      where    e.name = emp.name    and    e.chk >= emp.chk) > 1  
 
再把添加的列删掉，出现结果。 
 
alter     table    emp    drop     column    chk 

 
( 3 )另一个思路： 
视图 
 
select     min (chk)  from    emp  group     by    name  having     count ( * )    > 1  

 获得有重复的记录chk最小的值,于是可以 
 
delete   from    emp  where  chk    not     in  ( select   min (chk)  from    emp  group     by    name) 

写成join的形式也可以: 
 
( 1 )有例表：emp 
 
emp_no    name    age     

    001             Tom      17      

    002             Sun       14      

    003             Tom      15      

    004             Tom      16  

 ◆要求生成序列号 
( 1 )最简单的方法，根据b问题的解法： 
 
alter     table    emp    add    chk    int     identity ( 1 , 1 )   或   

    select     * , identity ( int , 1 , 1 )   chk    into    #tmp    from    emp 

 ◆如果需要控制顺序怎么办？ 
 
select     top     100000     * , identity ( int , 1 , 1 )   chk    into    #tmp    from    emp    order     by    age 

 ( 2 ) 假如不可以更改表结构，怎么办？ 
如果不可以唯一区分每条记录是没有办法的，在可以唯一区分每条记录的时候,可以使用a 中的count的思路解决这个问题 
 
select    emp. * ,( select     count ( * )    from    emp   e    where    e.emp_no <= emp.emp_no)   

    from    emp   

    order     by    ( select     count ( * )    from    emp   e    where    e.emp_no <= emp.emp_no)