用一些不常见的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语句实现我们意想不到的功能的介绍就到这里了，希望本次的介绍能够带给您一些收获！

【编辑推荐】

1. ASP.NET连接Oracle数据库的步骤详解
2. MySQL数据库各种存储引擎及其作用简介
3. 介绍几种大型的Oracle/SQL Server数据库免费版
4. SQL Server数据库使用Insert语句实现数据的复制
5. 如何通过扩展属性为SQL Server的索引添加注释信息