SQL語句是一種集合操作，就是批量操作，它的速度要比其他的語言快，所以在設(shè)計(jì)的時(shí)候很多的邏輯都會(huì)放在sql語句或者存儲(chǔ)過程中來實(shí)現(xiàn)，這個(gè)是一種設(shè)計(jì)思想。但是今天我們來討論另外一個(gè)話題。Sql頁提供了豐富的函數(shù)供我們使用，還有很多操作有意想不到的結(jié)果，今天這個(gè)隨筆來看看一些不常見到的sql語句。這些語句不像普通的增刪查那樣平白，它的奇妙之處有時(shí)候讓人另眼相看。

1. 假設(shè)我想把Person.Contact表中所有人的名字用逗號連接起來，串成一個(gè)字符串，可能會(huì)想到使用游標(biāo)把FirstName查出來然后逐行賦值給一個(gè)字符串變量，可是使用游標(biāo)的代價(jià)是很大的?？纯聪旅娴拇a：

declare @names varchar(1000)=''—注意賦值為空字符串是必須的 
select @names=isnull(@names,'')+FirstName+',' from Person.Contact 
print @names 

查詢得到的結(jié)果是(用的是AdventureWorks數(shù)據(jù)庫中的Contact表)：
Gustavo,Catherine,Kim,Humberto,Pilar,Frances,Margaret,Carla,Jay,Ronald,Samuel,James,
Robert,Fran? ois,Kim,Lili,Amy,Anna,Milton,Paul,Gregory,J.Phillip,
Michelle,Sean,Phyllis,Marvin,Michael,Cecil,Oscar,Sandra,Selena,Emilio,
Maxwell,Mae,Ramona,Sabria,Hannah,Kyley,Tom,Thomas,John,Chris,

使用其他的語句是不能達(dá)到這個(gè)效果的，不過我沒有深入考慮過，但是這個(gè)是很簡單的語句。

還有一個(gè)地方和這個(gè)類似，就是在行列轉(zhuǎn)換的時(shí)候拼接動(dòng)態(tài)sql語句，首先使用下面的語句創(chuàng)建一個(gè)臨時(shí)表：

create table #DepartCost 
( 
id int, 
Department varchar(20), 
Material varchar(20), 
Number int 
) 
insert into #DepartCost values 
(1,'廠房','材料',1), 
(1,'廠房','材料',2), 
(1,'廠房','材料',1), 
(1,'廠房','材料',1), 
(1,'廠房','材料',1), 
(1,'廠房','材料',1), 
(1,'廠房','材料',2), 
(1,'廠房','材料',1), 
(1,'廠房','材料',1) 

表中的數(shù)據(jù)如下：

圖1

我們看到每個(gè)廠房分別使用的材料數(shù)量，還是一個(gè)老問題，如果我們想知道針對每種材料，每個(gè)廠房耗費(fèi)的材料數(shù)量是多少該怎么寫呢。有一種笨的方法，如下：

select Department, 
sum(case Material when '材料1' then Number else 0 end) as [材料], 
sum(case Material when '材料2' then Number else 0 end) as [材料], 
sum(case Material when '材料3' then Number else 0 end) as [材料] 
from #DepartCost 
group by Department 

查詢結(jié)果如下：

圖2

說這種方法笨是因?yàn)樾枰孪戎啦牧系念悇e，如果有很多種材料這個(gè)語句就會(huì)很長了，下面我們使用動(dòng)態(tài)語句來實(shí)現(xiàn)這個(gè)功能：

declare @sql varchar(1000) 
set @sql = 'select Department ' 
select @sql = @sql+', sum(case Material when '''+Material+''' then Number else 0 end) as ['+Material+']' from 
(select distinct Material from #DepartCost) as a 
select @sql = @sql + ' from #DepartCost group by Department ' 
exec(@sql) 

我們來看看@sql字符串變量到底長得什么樣子，使用print @sql將它打印出來：

select Department , sum(case Material when '材料' then Number else 0 end) as [材料], sum(case Material when '材料' then Number else 0 end) as [材料], sum(case Material when '材料' then Number else 0 end) as [材料] from #DepartCost group by Department

這個(gè)語句和上面那個(gè)是一樣的，當(dāng)然exec(@sql)得到的結(jié)果也是一樣的了。這里我不知道這種特性有個(gè)什么說法，不像子查詢，也不是case語句。

2.寫一個(gè)語句獲得當(dāng)前這個(gè)月有多少天

這個(gè)涉及到日期和時(shí)間，初步的思路是查詢得到本月的***一天，然后用datepart獲得天數(shù)，這是一個(gè)很直接的方法。來看下面的語句：

select  
datepart( 
dd,--datepart的參數(shù)取本月***一天的天數(shù)，即為本月的天數(shù) 
dateadd(dd,--取下個(gè)月的***天的前一天，就是本月***一天 
        -1, 
        dateadd(mm,--取下一個(gè)月的***天 
                1, 
                cast(cast(year(getdate())as varchar)+'-'+ --取當(dāng)前的年 
                cast(month(getdate()) as varchar)+'-01'--取這個(gè)月的***天 
                as datetime))) --轉(zhuǎn)換成時(shí)間 
) 

這個(gè)語句沒有什么懸念，僅僅是時(shí)間函數(shù)的使用，只要知道這個(gè)思路就很容易寫出來。

3.假設(shè)我們有一張銷售表，現(xiàn)在要查出銷售單價(jià)，但是我們想不適用具體的價(jià)錢來顯示，而是顯示為一個(gè)范圍，比如價(jià)錢是1-100元要顯示“1 to 100”，100-200要顯示“100 to 200”，等等。來看代碼：

elect so.UnitPrice, NewUnitPrice =   
case when so.UnitPrice is null then 'unknown' --NewPrice一點(diǎn)類似于C#里面的var變量，事先不定義類型，從賦值結(jié)果里面確認(rèn)它的類型 
     when so.UnitPrice between 100 and 200 then '100 to 200' 
     when so.UnitPrice between 201 and 300 then '200 to 300' 
     when so.UnitPrice between 301 and 400 then '300 to 400' 
     else cast(so.UnitPrice as varchar(10)) --這里一定要轉(zhuǎn)換成字符串 
     end 
from Sales.SalesOrderDetail so order by UnitPrice 

要注意的是***剩下一些不做歸類轉(zhuǎn)換的必須將類型轉(zhuǎn)換為varchar，否則會(huì)有語法錯(cuò)誤。結(jié)果如下：

圖3

4.假設(shè)有一張聯(lián)系人姓名表，現(xiàn)在想查出這個(gè)表中姓相同的聯(lián)系人的數(shù)目，猛一看有點(diǎn)懵，其實(shí)很簡單，來看代碼：

select c.LastName,num_LastName=COUNT(1) from Person.Contact c group by c.LastName 

圖4

注意要統(tǒng)計(jì)那個(gè)字段就要對那個(gè)字段進(jìn)行聚合操作，如圖我們可以看到有77個(gè)姓Davis的，71個(gè)姓Lin的，90個(gè)姓Waston的等等。

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/08/01/2124046.html

【編輯推薦】

1. 淺述遠(yuǎn)程Service Broker的實(shí)現(xiàn)
2. Service Broker基礎(chǔ)應(yīng)用(下）
3. Service Broker基礎(chǔ)應(yīng)用(上）
4. 簡述Service Broker事件通知功能