程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 教你輕松解決幾種常見的SQL疑難問題

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

編輯:關於SqlServer

常見的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個人名字與這條記錄相同的就是合格的 ,就有

 

ordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="#000000" border="1">

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)


  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved