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
这个方法很一般，求教高手们提供一个更加灵活的方法。
