這輩子寫過的比較有意思的幾個sql

遞歸

with myRecursion as(

select * from recursion where id=1

union all select r.* from myRecursion m,recursion r where m.id=r.pid

)

select * from myRecursion

Ps:union all 不去重求並集

很多地方都用到了遞歸，比如asp.net mvc里的模型綁定就是遞歸綁定的，還比如樹狀菜單

排名

下表是一個銷售業績表，我對銷售業績做一個排名，顯示出排名結果

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank

FROM ranking  a1, ranking  a2

WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

GROUP BY a1.Name, a1.Sales

ORDER BY a1.Sales DESC, a1.Name DESC;

結果：

重點是自己和自己比較，找出a1的Sales小於a2的Sales的數據 或者Name和Sales都相等的數據(a1里的全部數據去對比a2里的每一個數據）

未分組的結果：

SELECT a1.Name, a1.Sales, a2.sales Sales_Rank

FROM ranking  a1, ranking  a2

WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

結果如圖，結果一目瞭然。只要分組count一下就是排名了。

其實還有一個問題就是有併列排名，比如上圖中有個併列第3的，第四名就不存在了。

這些都可根據具體的規則用程式去調整，嘻嘻

去重

有時我們會遇到一些表裡有些重覆的數據，如圖：

第一種，去除全部重覆的數據除id以外

declare @t1 table(id int,name nchar(10),Text nchar(10))

insert into @t1(Name,Text)(select distinct Name,Text from mydistinct1)

delete from mydistinct1

insert into mydistinct1(name,text)(select name,text from @t1)

Ps:@t 定義一個虛擬表，向虛擬表裡插入用distinct去重的數據，清空原表，再把虛擬表裡的數據插入到原表。

第二種，去除指定列重覆的數據。

delete from mydistinct where id not in(select MIN(id) from mydistinct group by name)

Ps:sql很簡單，分組後取分組裡一個id,這裡取最小的一個，刪除除此之外的id

行轉列

select 姓名 as 姓名 ,

  max(case 課程 when '語文' then 分數 else 0 end) 語文,

  max(case 課程 when '數學' then 分數 else 0 end) 數學,

  max(case 課程 when '物理' then 分數 else 0 end) 物理

from tb

group by 姓名

結果如圖：

Ps:一目瞭然就不多解釋了

For xml path 現實分組後指定列的全部數據

數據表：

For xml path 結果如下：

select * from forxmlpath for XML path('')

分組結果：

select name,min(text) from forxmlpath group by name

分組後除此分組列，其他列要顯示就要使用聚合函數，只能顯示結果中的一個或數量或合計

我們可以利用for xml path的特性把全部數據都顯示在一列中，並指定顯示格式

逗號間隔：

select text+',' from forxmlpath for xml path('')

逗號間隔顯示分組後非分組列：

select name,(select text+',' from forxmlpath where a.name=name for XML path('')) 

from forxmlpath a group by name

去掉結尾的逗號：

使用 left函數截取

select name,LEFT(text,LEN(text)-1)text

from(select name,(select text+',' from forxmlpath where a.name=name for XML path('')) text

from forxmlpath a group by name)t