SQL点滴之几个有点偏的语句

ㄣ负二代

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,'厂房','材料',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”，等等。来看代码：

elect 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的等等。

linxueyue

查询得到的结果是(用的是AdventureWorks数据库中的Contact表)：

linxueyue

数据库 sql语句问题

zm110110

看起来好强大的样子

宇智波·佐助

太强大了   

hielee

好起来好强大