SQL点滴21—几个有点偏的语句

SQL语句是一种集合操作，就是批量操作，它的速度要比其他的语言快，所以在设计的时候很多的逻辑都会放在sql语句或者存储过程中来实现，这个是一种设计思想。但是今天我们来讨论另外一个话题。Sql页提供了丰富的函数供我们使用，还有很多操作有意想不到的结果，今天这个随笔来看看一些不常见到的sql语句。这些语句不像普通的增删查那样平白，它的奇妙之处有时候让人另眼相看。

1.  假设我想把Person.Contact表中所有人的名字用逗号连接起来，串成一个字符串，可能会想到使用游标把FirstName查出来然后逐行赋值给一个字符串变量，可是使用游标的代价是很大的。看看下面的代码：

declare@namesvarchar(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语句，首先使用下面的语句创建一个临时表：

createtable #DepartCost
(
id int,
Department varchar(20),
Material varchar(20),
Numberint
)
insertinto #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'thenNumberelse0end) as[材料1],
sum(case Material when'材料2'thenNumberelse0end) as[材料2],
sum(case Material when'材料3'thenNumberelse0end) as[材料3]
from #DepartCost
groupby Department

查询结果如下：

图2

说这种方法笨是因为需要事先知道材料的类别，如果有很多种材料这个语句就会很长了，下面我们使用动态语句来实现这个功能：

declare@sqlvarchar(1000)
set@sql='select Department '
select@sql=@sql+', sum(case Material when '''+Material+''' then Number else 0 end) as ['+Material+']'from
(selectdistinct 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())asvarchar)+'-'+--取当前的年
cast(month(getdate()) asvarchar)+'-01'--取这个月的第一天
asdatetime))) --转换成时间
)

这个语句没有什么悬念，仅仅是时间函数的使用，只要知道这个思路就很容易写出来。

　　

3.假设我们有一张销售表，现在要查出销售单价，但是我们想不适用具体的价钱来显示，而是显示为一个范围，比如价钱是1-100元要显示“1 to 100”，100-200要显示“100 to 200”，等等。来看代码：

select so.UnitPrice, NewUnitPrice =  
casewhen so.UnitPrice isnullthen'unknown'--NewPrice一点类似于C#里面的var变量，事先不定义类型，从赋值结果里面确认它的类型
when so.UnitPrice between100and200then'100 to 200'
     when so.UnitPrice between201and300then'200 to 300'
     when so.UnitPrice between301and400then'300 to 400'
     elsecast(so.UnitPrice asvarchar(10)) --这里一定要转换成字符串
end
from Sales.SalesOrderDetail so orderby UnitPrice

要注意的是最后剩下一些不做归类转换的必须将类型转换为varchar，否则会有语法错误。结果如下：

图3

　　

 

4.假设有一张联系人姓名表，现在想查出这个表中姓相同的联系人的数目，猛一看有点懵，其实很简单，来看代码：

select c.LastName,num_LastName=COUNT(1) from Person.Contact c groupby 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

这个方法很一般，求教高手们提供一个更加灵活的方法。