常見的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)