經(jīng)常有人提到,用動(dòng)態(tài)生成SQL語(yǔ)句的方法處理數(shù)據(jù)時(shí),處理語(yǔ)句超長(zhǎng),無(wú)法處理的問(wèn)題
下面就討論這個(gè)問(wèn)題:
/*-- 數(shù)據(jù)測(cè)試環(huán)境 --*/
if exists (select * from dbo.sysobjects where id = object_id(N‘[tb]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)
drop table [tb]
GO
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
/*-- 要求結(jié)果
日期 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
--*/
/*-- 常規(guī)處理方法
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)‘)
--*/
/*-- 問(wèn)題 --*/
如果單位很多,這時(shí),@SQL的值就會(huì)被截?cái)?從而出錯(cuò).
下面給出三種解決辦法:
--/*-- 方法1. 多個(gè)變量處理
--定義變量,估計(jì)需要多少個(gè)變量才能保存完所有數(shù)據(jù)
declare @sql0 varchar(8000),@sql1 varchar(8000)
--,...@sqln varchar(8000)
--生成數(shù)據(jù)處理臨時(shí)表
select id=identity(int,0,1),groupid=0
,值=‘,[‘+單位名稱 +‘]=sum(case 單位名稱 when ‘‘‘
+單位名稱+‘‘‘ then 銷售額 else 0 end)‘
into #temp from(select distinct 單位名稱 from tb) a
--分組臨時(shí)表,判斷慨最多多少個(gè)單位可以組合成一個(gè)不超過(guò)8000的字符串,這里取假設(shè)為5個(gè)
update #temp set groupid=id/5 --5為每組的單位個(gè)數(shù)
--生成SQL語(yǔ)句處理字符串
--初始化
select @sql0=‘‘
,@sql1=‘‘
-- ...
-- ,@sqln
--得到處理字符串
select @sql0=@sql0+值 from #temp where groupid=0 --第一個(gè)變量
select @sql1=@sql1+值 from #temp where groupid=1 --第二個(gè)變量
--select @sqln=@sqln+值 from #temp where groupid=n --第n個(gè)變量
--查詢
exec(‘select 日期=convert(varchar(10),日期,120)‘
+@sql0+@sql1
-- ...+@sqln
+‘ from tb group by convert(varchar(10),日期,120)
‘)
--刪除臨時(shí)表
drop table #temp
/*
優(yōu)點(diǎn):比較靈活,數(shù)據(jù)量大時(shí)只需要增加變量就行了.不用改動(dòng)其他部分
缺點(diǎn):要自行估計(jì)處理的數(shù)據(jù),估計(jì)不足就會(huì)出錯(cuò)
*/
--*/
--/*--方法2. bcp+isql
--因?yàn)橐玫絙cp+isql,所以需要這些信息
declare @servername varchar(250),@username varchar(250),@pwd varchar(250)
select @servername=‘zj‘ --服務(wù)器名
,@username=‘‘ --用戶名
,@pwd=‘‘ --密碼
declare @tbname varchar(50),@sql varchar(8000)
--創(chuàng)建數(shù)據(jù)處理臨時(shí)表
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)
--生成創(chuàng)建視圖的文件,注意使用了文件:c:\temp.txt
set @sql=‘bcp "‘+@tbname+‘" out "c:\temp.txt" /S"‘
+@servername+‘" /U"‘+@username+‘" /P"‘+@pwd+‘" /c‘
exec master..xp_cmdshell @sql
--刪除臨時(shí)表
set @sql=‘drop table ‘+@tbname
exec(@sql)
--調(diào)用isql生成數(shù)據(jù)處理視圖
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
--調(diào)用視圖,顯示處理結(jié)果
set @sql=‘select * from ‘+@tbname+‘
drop view ‘+@tbname
exec(@sql)
/*
優(yōu)點(diǎn):程序自動(dòng)處理,不存在判斷錯(cuò)誤的問(wèn)題
缺點(diǎn):復(fù)雜,經(jīng)過(guò)的步驟多,容易出錯(cuò),而且需要一定的操作員權(quán)限
*/
--*/
--/*-- 方法3. 多個(gè)變量處理,綜合了方法1及方法2的優(yōu)點(diǎn), 解決了方法1中需要人為判斷的問(wèn)題,自動(dòng)根據(jù)要處理的數(shù)據(jù)量進(jìn)行變量定義,同時(shí)又避免了方法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)
--生成數(shù)據(jù)處理臨時(shí)表
select id=identity(int,0,1),gid=0
,a=‘,[‘+單位名稱 +‘]=sum(case 單位名稱 when ‘‘‘
+單位名稱+‘‘‘ then 銷售額 else 0 end)‘
into # from(select distinct 單位名稱 from tb) a
--判斷需要多少個(gè)變量來(lái)處理
select @i=max(len(a)) from #
print @i
set @i=7800/@i
--分組臨時(shí)表
update # set gid=id/@i
select @i=max(gid) from #
--生成數(shù)據(jù)處理語(yǔ)句
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)
--執(zhí)行
exec( @sql1+@sql2+@sql3+‘
exec(‘+@sqlhead+‘+‘+@sql4+‘+‘+@sqlend+‘)‘
)
--刪除臨時(shí)表
drop table #
--*/
方法3中,關(guān)鍵要做修改的是下面兩句,其他基本上不用做改變:
--生成數(shù)據(jù)處理臨時(shí)表,修改a=后面的內(nèi)容為相應(yīng)的處理語(yǔ)句
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
--生成數(shù)據(jù)處理語(yǔ)句,將@sqlhead,@sqlend賦值為相應(yīng)的處理語(yǔ)句頭和尾
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=‘‘
