化解字符串不能超过8000的方法-优快云博客

本文介绍了一种在SQL中处理大量数据并生成复杂报表的方法，针对动态生成SQL语句超长的问题提供了三种解决方案。

http://www.cnblogs.com/lcooc/articles/692201.html

经常有人提到 , 用动态生成 SQL 语句的方法处理数据时 , 处理语句超长 , 无法处理的问题

下面就讨论这个问题 :

/*-- 数据测试环境 --*/

if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [tb]

create table tb( 单位名称 varchar(10), 日期 datetime, 销售额 int)

insert into tb

select 'A 单位 ','2001-01-01',100

union all select 'B 单位 ','2001-01-02',101

union all select 'C 单位 ','2001-01-03',102

union all select 'D 单位 ','2001-01-04',103

union all select 'E 单位 ','2001-01-05',104

union all select 'F 单位 ','2001-01-06',105

union all select 'G 单位 ','2001-01-07',106

union all select 'H 单位 ','2001-01-08',107

union all select 'I 单位 ','2001-01-09',108

union all select 'J 单位 ','2001-01-11',109

/*-- 要求结果

日期 A 单位 B 单位 C 单位 D 单位 E 单位 F 单位 G 单位 H 单位 I 单位 J 单位

---------- ----- ----- ----- ----- ----- ----- ---- ---- ---- ------

2001-01-01 100 0 0 0 0 0 0 0 0 0

2001-01-02 0 101 0 0 0 0 0 0 0 0

2001-01-03 0 0 102 0 0 0 0 0 0 0

2001-01-04 0 0 0 103 0 0 0 0 0 0

2001-01-05 0 0 0 0 104 0 0 0 0 0

2001-01-06 0 0 0 0 0 105 0 0 0 0

2001-01-07 0 0 0 0 0 0 106 0 0 0

2001-01-08 0 0 0 0 0 0 0 107 0 0

2001-01-09 0 0 0 0 0 0 0 0 108 0

2001-01-11 0 0 0 0 0 0 0 0 0 109

--*/

/*-- 常规处理方法 */

declare @sql varchar(8000)

set @sql='select 日期 =convert(varchar(10), 日期 ,120)'

select @sql=@sql+',['+ 单位名称

+']=sum(case 单位名称 when '''+ 单位名称 +''' then 销售额 else 0 end)'

from(select distinct 单位名称 from tb) a

exec(@sql+' from tb group by convert(varchar(10), 日期 ,120)')

/*-- 问题 : 如果单位很多 , 这时 ,@SQL 的值就会被截断 , 从而出错 .*/

/*-- 下面给出三种解决办法 :--*/

--/*-- 方法 1. 多个变量处理

-- 定义变量 , 估计需要多少个变量才能保存完所有数据

declare @sql0 varchar(8000),@sql1 varchar(8000)

--,...@sqln varchar(8000)

-- 生成数据处理临时表

select id=identity(int,0,1),groupid=0

, 值 =',['+ 单位名称 +']=sum(case 单位名称 when '''

+ 单位名称 +''' then 销售额 else 0 end)'

into #temp from(select distinct 单位名称 from tb) a

-- 分组临时表 , 判断慨最多多少个单位可以组合成一个不超过 8000 的字符串 , 这里取假设为 5 个

update #temp set groupid=id/5 --5 为每组的单位个数

-- 生成 SQL 语句处理字符串

-- 初始化

select @sql0=''

,@sql1=''

-- ...

-- ,@sqln

-- 得到处理字符串

select @sql0=@sql0+ 值 from #temp where groupid=0 -- 第一个变量

select @sql1=@sql1+ 值 from #temp where groupid=1 -- 第二个变量

--select @sqln=@sqln+ 值 from #temp where groupid=n -- 第 n 个变量

-- 查询

exec('select 日期 =convert(varchar(10), 日期 ,120)'

+@sql0+@sql1

-- ...+@sqln

+' from tb group by convert(varchar(10), 日期 ,120)

-- 删除临时表

drop table #temp

优点 : 比较灵活 , 数据量大时只需要增加变量就行了 . 不用改动其他部分

缺点 : 要自行估计处理的数据 , 估计不足就会出错

--*/

--/*-- 方法 2. bcp+isql

-- 因为要用到 bcp+isql, 所以需要这些信息

declare @servername varchar(250),@username varchar(250),@pwd varchar(250)

select @servername='zj' -- 服务器名

,@username='' -- 用户名

,@pwd='' -- 密码

declare @tbname varchar(50),@sql varchar(8000)

-- 创建数据处理临时表

set @tbname='[##temp_'+convert(varchar(40),newid())+']'

set @sql='create table '+@tbname+'( 值 varchar(8000))

insert into '+@tbname+' values(''create view '

+stuff(@tbname,2,2,'')+' as

select 日期 =convert(varchar(10), 日期 ,120)'')'

exec(@sql)

set @sql='insert into '+@tbname+'

select '',[''+ 单位名称 +'']=sum(case 单位名称 when ''''''

+ 单位名称 +'''''' then 销售额 else 0 end)''

from(select distinct 单位名称 from tb) a'

exec(@sql)

set @sql='insert into '+@tbname+'

values(''from tb group by convert(varchar(10), 日期 ,120)'')'

exec(@sql)

-- 生成创建视图的文件 , 注意使用了文件 :c:/temp.txt

set @sql='bcp "'+@tbname+'" out "c:/temp.txt" /S"'

+@servername+'" /U"'+@username+'" /P"'+@pwd+'" /c'

exec master..xp_cmdshell @sql

-- 删除临时表

set @sql='drop table '+@tbname

exec(@sql)

-- 调用 isql 生成数据处理视图

set @tbname=stuff(@tbname,2,2,'')

set @sql='isql /S"'+@servername

+case @username when '' then '" /E' else '" /U"'+@username+'" /P"'+@pwd+'"' end

+' /d"'+db_name()+'" /i"c:/temp.txt"'

exec master..xp_cmdshell @sql

-- 调用视图 , 显示处理结果

set @sql='select * from '+@tbname+'

drop view '+@tbname

exec(@sql)

优点 : 程序自动处理 , 不存在判断错误的问题

缺点 : 复杂 , 经过的步骤多 , 容易出错 , 而且需要一定的操作员权限

--*/

--/*-- 方法 3. 多个变量处理 , 综合了方法 1 及方法 2 的优点 , 解决了方法 1 中需要人为判断的问题 , 自动根据要处理的数据量进行变量定义 , 同时又避免了方法 2 的繁琐

declare @sqlhead varchar(8000),@sqlend varchar(8000)

,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)

,@i int,@ic varchar(20)

-- 生成数据处理临时表

select id=identity(int,0,1),gid=0

,a=',['+ 单位名称 +']=sum(case 单位名称 when '''

+ 单位名称 +''' then 销售额 else 0 end)'

into # from(select distinct 单位名称 from tb) a

-- 判断需要多少个变量来处理

select @i=max(len(a)) from #

print @i

set @i=7800/@i

-- 分组临时表

update # set gid=id/@i

select @i=max(gid) from #

-- 生成数据处理语句

select @sqlhead='''select 日期 =convert(varchar(10), 日期 ,120)'''

,@sqlend=''' from tb group by convert(varchar(10), 日期 ,120)'''

,@sql1='',@sql2='select ',@sql3='',@sql4=''

while @i>=0

select @ic=cast(@i as varchar),@i=@i-1

,@sql1='@'+@ic+' varchar(8000),'+@sql1

,@sql2=@sql2+'@'+@ic+'='''','

,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic

+char(13)+@sql3

,@sql4=@sql4+',@'+@ic

select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)

,@sql2=left(@sql2,len(@sql2)-1)+char(13)

,@sql3=left(@sql3,len(@sql3)-1)

,@sql4=substring(@sql4,2,8000)

-- 执行

exec( @sql1+@sql2+@sql3+'

exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'

)

-- 删除临时表

drop table #

--*/

方法 3 中 , 关键要做修改的是下面两句 , 其他基本上不用做改变 :

-- 生成数据处理临时表 , 修改 a= 后面的内容为相应的处理语句

select id=identity(int,0,1),gid=0

,a=',['+code+']=sum(case b.c_code when '''

+code+''' then b.value else 0 end)'

into # from #Class

-- 生成数据处理语句 , 将 @sqlhead,@sqlend 赋值为相应的处理语句头和尾

select @sqlhead='''select a.id,a.name,a.code'''

,@sqlend=''' from #Depart a,#Value b where a.Code=b.d_Code group by a.id,a.code,a.name'''

,@sql1='',@sql2='select ',@sql3='',@sql4=''