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

sql 专栏收录该内容
23 篇文章 0 订阅

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
复制代码
这个方法很一般,求教高手们提供一个更加灵活的方法。
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值