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

朴灿烈づ我的快乐病毒、 2022-08-09 11:48 296阅读 0赞

选择重复，消除重复和选择出序列  
![None.gif][]  
![None.gif][]有例表：emp  
![None.gif][]  
![None.gif][]emp\_no name age

**001**  Tom  **17**

**002**  Sun  **14**

**003**  Tom  **15**

**004**  Tom  **16**   
![None.gif][]  
![None.gif][]要求：  
![None.gif][]  
![None.gif][]列出所有名字重复的人的记录  
![None.gif][]  
![None.gif][]( **1** )最直观的思路：要知道所有名字有重复人资料，首先必须知道哪个名字重复了：  
![None.gif][]  
![None.gif][] select  name  from  emp  group  by  name  having  count ( \* ) > **1**   
![None.gif][]  
![None.gif][]  
![None.gif][] 所有名字重复人的记录是:  
![None.gif][]  
![None.gif][] select  \*  from  emp

where  name  in  ( select  name  from  emp  group  by  name  having  count ( \* ) > **1** )  
![None.gif][]  
![None.gif][]( **2** )稍微再聪明一点，就会想到，如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有  
![None.gif][]  
![None.gif][] select  \*  from  emp  where  ( select  count ( \* )  from  emp e  where  e.name = emp.name)  > **1**   
![None.gif][]  
![None.gif][] \-- 注意一下这个>1，想下如果是 =1，如果是 =2 如果是>2 如果 e 是另外一张表 而且是=0那结果 就更好玩了:)    
![None.gif][]   
![None.gif][]这个过程是 在判断工号为001的 人 的时候先取得 001的 名字（emp.name） 然后和原表的名字进行比较 e.name  
![None.gif][]  
![None.gif][]注意e是emp的一个别名。  
![None.gif][]  
![None.gif][]再稍微想得多一点，就会想到，如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求：  
![None.gif][]  
![None.gif][] select  \*  from  emp

where  exists

( select  \*  from  emp e  where  e.name = emp.name  and  e.emp\_no <> emp.emp\_no)  
![None.gif][]  
![None.gif][] 此思路的join写法：  
![None.gif][]  
![ExpandedBlockStart.gif][] 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**   
![None.gif][]  
![None.gif][] \-- \--------------------------------------------------清除重复----------------------------------------------------   
![None.gif][] 过滤掉所有多余的重复记录  
![None.gif][]( **1** )我们知道distinct、 group  by  可以过滤重复，于是就有最直观的  
![None.gif][]  
![None.gif][] select  distinct  \*  from  emp 或  select  name,age  from  emp  group  by  name,age  
![None.gif][]  
![None.gif][]获得需要的数据，如果可以使用临时表就有解法：  
![None.gif][]  
![None.gif][] select  distinct  \*  into  \#tmp   from  emp

delete  from  emp

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

Tom  **16**  **1**

Sun  **14**  **2**

Tom  **16**  **3**

Tom  **16**  **4**   
![None.gif][]  
![None.gif][]重复记录可以表示为：  
![None.gif][]  
![None.gif][] select  \*  from  emp  where  ( select  count ( \* )  from  emp e  where  e.name = emp.name) > **1**   
![None.gif][]  
![None.gif][] 要删除的是：  
![None.gif][]  
![None.gif][] delete  from  emp

where  ( select  count ( \* )  from  emp e  where  e.name = emp.name  and  e.chk >= emp.chk) > **1**   
![None.gif][]![None.gif][]  
![None.gif][]再把添加的列删掉，出现结果。  
![None.gif][]  
![None.gif][] alter  table  emp  drop  column  chk  
![None.gif][]  
![None.gif][]  
![None.gif][]( **3** )另一个思路：  
![None.gif][]视图  
![None.gif][]  
![None.gif][] select  min (chk)  from  emp  group  by  name  having  count ( \* )  > **1**   
![None.gif][]  
![None.gif][] 获得有重复的记录chk最小的值,于是可以  
![None.gif][]  
![None.gif][] delete  from  emp  where  chk  not  in  ( select  min (chk)  from  emp  group  by  name)  
![None.gif][]  
![None.gif][]写成join的形式也可以:  
![None.gif][]  
![None.gif][]( **1** )有例表：emp  
![None.gif][]  
![None.gif][]emp\_no name age

**001**  Tom  **17**

**002**  Sun  **14**

**003**  Tom  **15**

**004**  Tom  **16**   
![None.gif][]  
![None.gif][] ◆要求生成序列号  
![None.gif][]( **1** )最简单的方法，根据b问题的解法：  
![None.gif][]  
![None.gif][] alter  table  emp  add  chk  **int**  identity ( **1** , **1** ) 或

select  \* , identity ( **int** , **1** , **1** ) chk  into  \#tmp  from  emp  
![None.gif][]  
![None.gif][] ◆如果需要控制顺序怎么办？  
![None.gif][]  
![None.gif][] select  top  **100000**  \* , identity ( **int** , **1** , **1** ) chk  into  \#tmp  from  emp  order  by  age  
![None.gif][]  
![None.gif][] ( **2** ) 假如不可以更改表结构，怎么办？  
![None.gif][]如果不可以唯一区分每条记录是没有办法的，在可以唯一区分每条记录的时候,可以使用a 中的count的思路解决这个问题  
![None.gif][]  
![None.gif][] 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)

[None.gif]: /images/20220731/89084454470144ecbf644b285d621adb.png
[ExpandedBlockStart.gif]: /images/20220731/177de5c88b8c4ce9bfb317fa4507e2d5.png