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

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

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

查詢(xún)得到的結(jié)果是(用的是AdventureWorks數(shù)據(jù)庫(kù)中的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,

使用其他的語(yǔ)句是不能達(dá)到這個(gè)效果的，不過(guò)我沒(méi)有深入考慮過(guò)，但是這個(gè)是很簡(jiǎn)單的語(yǔ)句。

還有一個(gè)地方和這個(gè)類(lèi)似，就是在行列轉(zhuǎn)換的時(shí)候拼接動(dòng)態(tài)sql語(yǔ)句，首先使用下面的語(yǔ)句創(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è)老問(wèn)題，如果我們想知道針對(duì)每種材料，每個(gè)廠房耗費(fèi)的材料數(shù)量是多少該怎么寫(xiě)呢。有一種笨的方法，如下：

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 

查詢(xún)結(jié)果如下：

圖2

說(shuō)這種方法笨是因?yàn)樾枰孪戎啦牧系念?lèi)別，如果有很多種材料這個(gè)語(yǔ)句就會(huì)很長(zhǎng)了，下面我們使用動(dòng)態(tài)語(yǔ)句來(lá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) 

我們來(lái)看看@sql字符串變量到底長(zhǎng)得什么樣子，使用print @sql將它打印出來(lái)：

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è)語(yǔ)句和上面那個(gè)是一樣的，當(dāng)然exec(@sql)得到的結(jié)果也是一樣的了。這里我不知道這種特性有個(gè)什么說(shuō)法，不像子查詢(xún)，也不是case語(yǔ)句。

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

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

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è)語(yǔ)句沒(méi)有什么懸念，僅僅是時(shí)間函數(shù)的使用，只要知道這個(gè)思路就很容易寫(xiě)出來(lái)。

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

elect so.UnitPrice, NewUnitPrice =   
case when so.UnitPrice is null then 'unknown' --NewPrice一點(diǎn)類(lèi)似于C#里面的var變量，事先不定義類(lèi)型，從賦值結(jié)果里面確認(rèn)它的類(lèi)型 
     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 

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

圖3

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

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

圖4

注意要統(tǒng)計(jì)那個(gè)字段就要對(duì)那個(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. 簡(jiǎn)述Service Broker事件通知功能