SQL點滴21—幾個有點偏的語句

SQL語句是一種集合操作，就是批量操作，它的速度要比其他的語言快，所以在設計的時候很多的邏輯都會放在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',1),
(1,'廠房2','材料2',2),
(1,'廠房1','材料3',1),
(1,'廠房3','材料3',1),
(1,'廠房2','材料3',1),
(1,'廠房3','材料1',1),
(1,'廠房1','材料1',2),
(1,'廠房1','材料2',1),
(1,'廠房1','材料3',1)

表中的數據如下：

圖1

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

select Department,
sum(case Material when '材料1' then Number else 0 end) as [材料1],
sum(case Material when '材料2' then Number else 0 end) as [材料2],
sum(case Material when '材料3' then Number else 0 end) as [材料3]
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的等等。

5.查找數據庫中所有表的行數

select ROW_NUMBER() over(order by TABLE_NAME) as rownumber,TABLE_SCHEMA, TABLE_NAME into #table from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
declare @count int
select @count = COUNT(*) from #table
declare @index int = 1
declare @tablename nvarchar(200)
declare @sql nvarchar(1000)
while @index<@count
begin
select @tablename=TABLE_SCHEMA+'.'+TABLE_NAME from #table where rownumber=@index
select @sql= 'select '''+@tablename+''' as tablename, COUNT(*) as rowscount from '+ @tablename
exec (@sql)
if @index>@count
break
set @index = @index+1
end
drop table #table

這個方法很一般，求教高手們提供一個更加靈活的方法。

posted @ 2011-08-01 16:55 nd 閱讀(3704) 評論(22) 收藏舉報

刷新頁面返回頂部

SQL點滴21—幾個有點偏的語句

公告