SQL Server特殊功能实现 多行转一行

SQL Server特殊功能实现

(2014-07-11 16:52:42)

标签： sql 特殊查询 实现过程 字符串 排序

分类： 设计心得

1、给定字符串查找表

相信大家都有这样的经历，明明知道某个产品"XXX口服液"在数据库中是存在的，但又忘了在哪张表里面，查找各种资料又麻烦。这时候不禁感叹：要是能够根据“口服液”这三个字就能在对应的库中找到需要的记录该多好啊！别担心，下面的方法绝对让你心想事成！

use Database

go

if exists (select * from sysobjects where name = 'Full_Search')

drop proc Full_Search

go

create proc Full_Search(@old_string varchar(20))

as

begin

declare @tbname varchar(50)

declare tbroy cursor for select name from sysobjects where xtype= 'U ' --第一个游标遍历所有的表

open tbroy

fetch next from tbroy into @tbname

while @@fetch_status=0

begin

declare @colname varchar(50)

declare colroy cursor for select name from syscolumns where id=object_id(@tbname) and xtype in (select xtype from systypes where name in ( 'varchar', 'nvarchar', 'char', 'nchar', 'text')) --第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段

open colroy

fetch next from colroy into @colname

while @@fetch_status=0

begin

declare @sql nvarchar(1000),@j int

select @sql= 'select @i=count(1) from ' + '[' + @tbname + ']' + ' where '+ '[' + @colname + ']'+ ' like '+ '''%'+ @old_string + '%'''

--select @sql

exec dbo.sp_executesql @sql,N'@i int output', @i = @j output --输出满足条件表的记录数

if @j> 0

begin

select 包含字串的表名 = @tbname ,包含字串的列名 = @colname

--select @sql = 'select ' + @colname + ',' + ' from ' + @tbname + ' where '+ @colname + ' like '+ '''%' + @old_string + '%'''

--select @sql

--exec(@sql)

end

fetch next from colroy into @colname

end

close colroy

deallocate colroy

fetch next from tbroy into @tbname

end

close tbroy

deallocate tbroy

end

go

--exec Full_Search '口服液'

2、多行数据转一行

假设现在有一张表，名叫Grade，里面存放的是学生的成绩。如下所示：

select * from Grade

Name	Math	English	Chinese
小一	88	89	90
小二	78	79	80
小三	68	69	70
小四	58	59	60

现在想通过查询显示如下效果

小一

88

89

90

小二

78

79

80

......

那么该如何实现这样的功能呢？我们分析发现，这好像也不严格属于我们常常说的行转列或者列转行。其实我们可以把他想象成列转行的一般情况，就好比正比例函数是一次函数的特例一样。

废话不多说，下面是实现过程：

create table #Grade(id int identity(1,1), Name varchar(50), Math varchar(50), English varchar(50), Chinese varchar(50))

insert #Grade select * from Grade

--select * from #Grade

declare @var nvarchar(4000)

set @var=''

select @var=@var+','''+CAST(Name AS nvarchar(10))+ ''' AS '' Name '+ cast(id as nvarchar(2)) +''''

+','''+CAST(Math AS nvarchar(10))+ ''' AS '' Math '+ cast(id as nvarchar(2)) +''''

+','''+CAST(English AS nvarchar(10))+ ''' AS '' English '+ cast(id as nvarchar(2)) +''''

+','''+CAST(Chinese AS nvarchar(10))+ ''' AS '' Chinese '+ cast(id as nvarchar(2)) +''''

from #Grade

set @var=stuff(@var, 1, 1, '')    --去掉@var中的第一个逗号

select @var  --测试输出拼好的@var

exec('select '+ @var)  --在@var前添加select关键字,组成一条select语句,并执行这条语句

go

看到上面代码，有人就会觉得万一我的表有100多列甚至更多，难道每个列名都得列出来？其实这个当然没有必要， 第一个问题已经告诉了你答案，那就是循环遍历.

3、分割字符串

这个其实是个很有意思的功能。也是别人提出的一个问题，具体情境和需求大家可以参看： http://zhidao.baidu.com/question/135549363818113365.html?oldq=1

看完后你会发现他的主要目的和问题都很明显。目的就是分割FullPath，提取出省、市(区)、街道，根据省的AreaId来决定FullPath中含有该省但又没有街道的市(区)记录。 问题就是如何分割字符串以及排除街道呢？

相信大家看完下面的实现就很清楚了：

为了更加简洁的表述，我将例子更加简化：

create table District(FullPath varchar(100))

insert into District values('1,37')

insert into District values('1,45')

insert into District values('1,56')

insert into District values('2,34')

insert into District values('1,37,567')

--select * from District

下面的问题就是如何只显示第一、第二和第三条结果，请看实现：

if exists(select * from sysobjects where id =object_id('AreaSelectAllForFullPath'))

drop proc AreaSelectAllForFullPath

go

--创建存储过程

create proc AreaSelectAllForFullPath

@AreaId int

as

select * from district where (case when len(FullPath)-len(replace(FullPath,',','')) = 1 then left

(FullPath,CHARINDEX(',',FullPath,0)-1) else FullPath end)= cast(@AreaId as char(5))

and FullPath <> cast(@AreaId as char(5))

go

exec AreaSelectAllForFullPath 1

go

4、只知其一，盼知全貌

这个功能相对来说比较简单，但也是日常我们经常可能碰到的一种需求。

举个有趣的例子：假如你家有一本 族谱 ， 族谱记录了所有人(包括直系和旁系的)。而 族谱之外又有两本分别记录了直系和旁系的 族谱。这三本 族谱好比数据库的三张表，如下所示：

族谱（序号，系别，系序号）

直系（系序号，姓名）

旁系（系序号，姓名）

那么我们在数据库就可以创建如下三张表：

create table 族谱 (序号 int identity(1,1), 系别 char(5), 系序号 int)

create table 直系 (系序号 int identity(1,1),  姓名 varchar(50))

create table 旁系 (系序号 int identity(1,1),  姓名 varchar(50))

注：此处序号设为自增是只是为了方便，实际操作中不一定是这样的。

接下来我们向表中录入几条测试数据：

insert into 族谱 values ('直系',3)

insert into 族谱 values ('旁系',2)

insert into 族谱 values ('直系',2)

insert into 直系 values ('张三丰')

insert into 直系 values ('张翠山')

insert into 直系 values ('张无忌')

insert into 旁系 values ('殷素素')

insert into 旁系 values ('赵敏')

insert into 旁系 values ('周芷若')

插入数据后查询结果如下：

select * from 族谱

select * from 旁系

select * from 直系

现在问题就来了，假如我只知道族谱序号是3，我现在需要查到对应这个序号的人是谁？

那有人就会说了，很简单：直接在直系和旁系表select不就行了。确实是这样，但是万一涉及的表不止两个呢？要知道我们这个只是一个例子而已。

当然，万事总有解决办法的，闲话不多说，上实现方法：

declare @Sql nvarchar(1000)

declare @temp1 nvarchar(100)

declare @j char(5)

declare @xh int

set @xh = 3

set @temp1 = 'SELECT @tmp = 系别 FROM 族谱 WHERE 族谱.序号=' + cast(@xh as char(2))

exec sp_executesql @temp1,N'@tmp char(5) output', @tmp = @j output

set @sql = 'SELECT 姓名 FROM ' + @j + 'WHERE 系序号 =(SELECT 系序号 FROM 族谱 WHERE 族谱.序号='+cast(@xh as char(2))+')'

--select @sql

exec(@sql)

确实我们找到了我们想要查找的数据，那么假如我们只知道系序号是2，又该如何查找呢？聪明的你不妨试试看

5、按月统计查询

假如有一张 Sales(销售表)，表里面肯定有 jine(销售金额) 和 time(销售时间) 这两个字段，现在我们查出2013年7月份的数据如下：

select distinct convert(char(10), time, 120) as 销售时间， sum(jine) as 销售金额 from Sales where (time between '2013-07-01' and '2013-07-31')

group by convert(char(10),time, 120)

order by convert(char(10),time, 120)

现在我们想统计这个月哪些天有销售业绩(也就是有销售金额，哪些天没有)。为了方便起见，这里我们将上面查询出来的数据存入一张临时表中，如下：

select distinct convert(char(10), time, 120) as 销售时间， sum(jine) as 销售金额

into #temp

from Sales where (time between '2013-07-01' and '2013-07-31')

group by convert(char(10),time, 120)

order by convert(char(10),time, 120)

--select * from #temp

然后我们按天去统计，假如某天有销售业绩，我们就显示“有", 没有销售业绩就显示"无"，于是就有了下面的查询：

select (cast(month(销售时间)as varchar(2)) + '月'+ cast(day(销售时间) as varchar(2))+'日') as 日期
, (case when count(*)>0 then '有' else '无' end) as 有无业绩 from #temp
group by cast(month(销售时间)as varchar(2)) + '月'+ cast(day(销售时间) as varchar(2))+'日'

好像不对啊，怎么全部是"有"业绩的呢？没有业绩的怎么没有显示？难道还得挨个去找？一个月受得了，可是要是一两年的怎么办？当然，黄天不负苦心人！下面就是解决办法，一起看下吧：

declare @d table(time varchar(10))
declare @date varchar(10)
set @date='2013-07-01'
while @date<'2013-08-01'
begin
insert @d select @date
set @date=convert(varchar(10), dateadd(dd,1,cast(@date as datetime)),120)
end

select (cast(month(d.time)as varchar(2)) + '月'+ cast(day(d.time) as varchar(2))+'日') as 日期
,isnull((case when count(销售金额)>0 then '有' else '无' end),'') as 有无业绩
from @d d left join #temp t
on (cast(month(d.time)as varchar(2)) + '月'+ cast(day(d.time) as varchar(2))+'日')
= (cast(month(t.销售时间)as varchar(2)) + '月'+ cast(day(t.销售时间) as varchar(2))+'日')
group by (cast(month(d.time)as varchar(2)) + '月'+ cast(day(d.time) as varchar(2))+'日')

看，问题迎刃而解，之所以代码会这样麻烦的原因完全是因为日期显示格式为x月x日所引起，如果去掉这个限制，代码就会简单很多。但这也引发我们思考一个问题。在这种格式下的日期排序好像是乱的，不方便我们查看和统计，那么我们应该如何排序这样的日期呢？ 下一个例子我们会讲到对于这种特殊日期格式我们该怎么排序

6、时间排序

下面我们就来解决之前的时间排序问题。我们以下面的数据作为例子：

select * from timeorder order by 日期

很明显此时的日期是有点凌乱的，不是我们想要的结果，那我们很快就注意到，7月2日排在7月29日后面的原因是因为排序规则是”同位比较，数字在前，汉字在后“，这时我们可以巧妙的利用字符串的长度来规避这个问题，如下：

select * from timeorder order by len(日期),日期

看，此时的时间终于能够正常排序显示了。但是这样还是有问题，假如日期列中多了一个"8月1日"的记录：

insert into timeorder values('8月1日')

select * from timeorder order by len(日期),日期

问题又出现了，本来应该出现在最后的8月1日却显示在了第五条记录。那怎么解决呢？很简单，如下：

select * from timeorder order by  left(日期,1),len(日期),日期

但是还是有漏洞，比如多了一个"12月21日"这条记录：

insert into timeorder values('12月21日')

select * from timeorder order by  left(日期,1),len(日期),日期

此时，本来应该出现在最后的记录竟然显示在第一条，这可就说不过去了。那应该怎么解决这种问题呢？

在这里，我们提供两种可行方案：

第一种：动态取长度

其实，继续上面的思路，我们只需要能够将每个字符串前面的月份提取出来进行比较即可，关键就是如何区分到底是7月还是12月，在这里我们可以通过 charindex来实现我们的需求，具体如下：

select * from timeorder order by cast(left(日期,charindex('月', 日期)-1) as int),len(日期),日期

第二种：自定义函数

当我们用上面方法解决后，我们回头想想，其实这样的方法并不是最通用的。如果我们能够将汉字和数字分离开，再去比较那就更完美了。自定义函数可以轻松解决这样的问题：

if exists (select * from sysobjects where type = 'FN' and name = 'F_Get_No')

drop function F_Get_No

go

create  function F_Get_No

(

@No varchar(100)

)

returns bigint

as

begin

while patindex('%[^0-9]%',@No)>0

begin

declare @len int

set @len = len(SUBSTRING(@No,CHARINDEX('月',@No)+1,CHARINDEX('日',@No)-CHARINDEX('月',@No)-1))

if @len = 1

set @No=stuff(@No,patindex('%[^0-9]%',@No),1,'0') --删掉一个非数字的字符，循环结束，剩余的为数字部分

else

set @No=stuff(@No,patindex('%[^0-9]%',@No),1,'') --删掉一个非数字的字符，循环结束，剩余的为数字部分

end

return convert(bigint,@No)

end

其中@len变量是为了区分日期是个位还是十位，个位前面补0，十位不变。接下来，我们就可以调用该函数来帮助我们快速查询：

select * from timeorder order by dbo.F_Get_No(日期)

这样以后就可以通用了，当然针对具体需求函数是不一样的，这个大家可以自行修改。然而由此，我们也可以拓展，我如果想取出里面的汉字，该怎么下手呢？根据上面的函数修改其实不是很难。只需修改一下查询的正则表达式即可：

if exists (select * from sysobjects where type = 'FN' and name = 'F_GetChar')

drop function F_GetChar

go

create  function F_GetChar

(

@No varchar(100)

)

returns varchar(100)

as

begin

while patindex('%[^吖-咗]%',@No)>0

begin

set @No=stuff(@No,patindex('%[^吖-咗]%',@No),1,'') --删掉一个非汉字的字符，循环结束，剩余的为汉字部分

end

return convert(varchar(100),@No)

end

select dbo.F_GetChar(日期) as 汉字 from timeorder

更多精彩，敬请期待！