教你輕鬆解決幾種常見的SQL疑難問題

來源:互聯網
上載者:User

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



相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。