用一些不常見的SQL語句實現讓人意想不到的功能
在SQL Server數據庫中，我們平時使用SQL語句時，經常是一些常見的插入、刪除和查詢等操作，本文我們換一種方式，介紹一些SQL語句的另類功能，主要介紹了幾個使用一些不常見的SQL語句來實現一些功能的例子，這些功能使人眼前一亮，是我們意想不到的。接下來我們就開始一一介紹。
1. 假設我想把Person.Contact表中所有人的名字用逗號連接起來，串成一個字符串，可能會想到使用游標把FirstName查出來然后逐行賦值給一個字符串變量，可是使用游標的代價是很大的。看看下面的代碼：
- declare @names varchar(1000)=''—注意賦值為空字符串是必須的
- select @names=isnull(@names,'')+FirstName+',' from Person.Contact
- print @names
查詢得到的結果是（用的是AdventureWorks數據庫中的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,
使用其他的語句是不能達到這個效果的，不過我沒有深入考慮過，但是這個是很簡單的語句。
還有一個地方和這個類似，就是在行列轉換的時候拼接動態sql語句，首先使用下面的語句創建一個臨時表：
- 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)
表中的數據如下：
圖1
我們看到每個廠房分別使用的材料數量，還是一個老問題，如果我們想知道針對每種材料，每個廠房耗費的材料數量是多少該怎么寫呢。有一種笨的方法，如下：
- 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
查詢結果如下：
圖2
說這種方法笨是因為需要事先知道材料的類別，如果有很多種材料這個語句就會很長了，下面我們使用動態語句來實現這個功能：
- 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
這個語句和上面那個是一樣的，當然exec(@sql)得到的結果也是一樣的了。這里我不知道這種特性有個什么說法，不像子查詢，也不是case語句。
2.寫一個語句獲得當前這個月有多少天
這個涉及到日期和時間，初步的思路是查詢得到本月的最后一天，然后用datepart獲得天數，這是一個很直接的方法。來看下面的語句：
- select
- datepart(
- dd,--datepart的參數取本月最后一天的天數，即為本月的天數
- dateadd(dd,--取下個月的第一天的前一天，就是本月最后一天
- -1,
- dateadd(mm,--取下一個月的第一天
- 1,
- cast(cast(year(getdate())as varchar)+'-'+ --取當前的年
- cast(month(getdate()) as varchar)+'-01'--取這個月的第一天
- as datetime))) --轉換成時間
- )
這個語句沒有什么懸念，僅僅是時間函數的使用，只要知道這個思路就很容易寫出來。
3.假設我們有一張銷售表，現在要查出銷售單價，但是我們想不適用具體的價錢來顯示，而是顯示為一個范圍，比如價錢是1-100元要顯示“1 to 100”，100-200要顯示“100 to 200”，等等。來看代碼：
- select so.UnitPrice, NewUnitPrice =
- case when so.UnitPrice is null then 'unknown' --NewPrice一點類似于C#里面的var變量，事先不定義類型，從賦值結果里面確認它的類型
- 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)) --這里一定要轉換成字符串
- end
- from Sales.SalesOrderDetail so order by UnitPrice
要注意的是最后剩下一些不做歸類轉換的必須將類型轉換為varchar，否則會有語法錯誤。結果如下：
圖3
4.假設有一張聯系人姓名表，現在想查出這個表中姓相同的聯系人的數目，猛一看有點懵，其實很簡單，來看代碼：
- select c.LastName,num_LastName=COUNT(1) from Person.Contact c group by c.LastName
圖4
注意要統計那個字段就要對那個字段進行聚合操作，如圖我們可以看到有77個姓Davis的，71個姓Lin的，90個姓Waston的等等。
關于用一些不常見的SQL語句實現我們意想不到的功能的介紹就到這里了，希望本次的介紹能夠帶給您一些收獲！
【編輯推薦】
