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à)是很大的。看看下面的代碼：
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
'
,
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
)
表中的數(shù)據(jù)如下：
圖1
我們看到每個(gè)廠房分別使用的材料數(shù)量，還是一個(gè)老問題，如果我們想知道針對每種材料，每個(gè)廠房耗費(fèi)的材料數(shù)量是多少該怎么寫呢。有一種笨的方法，如下：
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
查詢結(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 ”，等等。來看代碼：
select
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 的等等。
5.查找數(shù)據(jù)庫中所有表的行數(shù)
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
這個(gè)方法很一般，求教高手們提供一個(gè)更加靈活的方法。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
