经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题
下面就讨论这个问题:
/*-- 数据测试环境 --*/
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
/*-- 要求结果
日期 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=''
分享到：
相关推荐
库卡机器人高级字符串处理指令文档是库卡机器人用户和开发者不可或缺的参考资料，旨在帮助用户快速掌握高级字符串处理指令的使用方法，提高生产效率和产品质量。 知识点： * CREAD/CWRITE编程指令的使用方法和技术...
字符串比较处理宏字符串比较处理宏字符串比较处理宏字符串比较处理宏
常用的文本处理方法，比如过滤关键词。文本编码。去除 htmlCode 中所有的HTML标签(包括标签中的属性)。截取字符串。将Gb2312编码的字符串转换为utf-8。判断是否有非法字符。分割字符串。检测含中文字符串实际长度。...
字符串处理函数是编程语言中非常重要的工具，它们用于操作、分析和操纵文本数据。以下是一些常见的字符串处理函数，这些函数通常在C语言或其他类似语言中使用，虽然这里没有提供具体的编程语言环境，但理解这些函数...
C语言字符串转换为Python字符串的方法 C语言字符串转换为Python字符串是指将C语言中的字符串数据转换为Python中的字符串对象，以便在Python环境中使用。下面详细介绍了C语言字符串转换为Python字符串的方法。 使用...
总结起来，C#中实现移除字符串末尾指定字符的方法主要涉及到字符串的`LastIndexOf`和`Substring`方法，通过这两个方法的组合，我们可以高效地完成字符串的处理，满足各种应用场景的需求。同时，单元测试对于确保代码...
indexOf() 方法可返回某个指定的字符串值在字符串中首次出现的位置。如果要检索的字符串值没有出现，则该方法返回 -1。 方法二:match() var str = "123" var reg = RegExp(/3/); if(str.match(reg)){ //包含； } ...
Delphi中处理字符串的相关方法 1、字符集转换方法 (1）stringtowidechar function stringtowidechar(const source:string;dest :pwidechar;destsize :integer) :pwidechar; 将默认string类型的字符串转换为unicode...
在实际项目中，这种自定义的字符串处理库往往能提高代码的可读性和可维护性，同时避免重复造轮子。 总的来说，这个"纯C++ 字符串处理函数大全源码"为C++开发者提供了一套实用的字符串操作工具，涵盖了字符串的分割...
在VB（Visual Basic）编程中，处理字符串是常见的任务之一，其中包括将一个长字符串拆分成多个子字符串。这个过程通常称为“字符串拆分”或“分隔字符串”。在这个主题中，我们将深入探讨VB中如何实现这一操作，以及...
在C#编程语言中，处理字符串是常见的任务之一，其中包括删除字符串中的特定部分或子字符串。本篇文章将详细探讨如何在C#中实现这一功能，包括多种方法和实用技巧。 首先，C#提供了多种内置方法来操作字符串，比如`...
本教程将深入探讨三个关键的字符串方法：`split()`、`strip()` 和 `translate()`，这些都是Python中处理字符串时非常实用的功能。 首先，`split()` 方法用于将字符串分割成多个子字符串，并返回一个包含这些子字符...
"C++字符串处理类库及范例"就是这样一个资源，它专门针对字符串操作进行了优化，提供了强大的功能，包括字符串的分割、自定义分隔符的使用、字符串匹配和搜索等。这个库无需编译和安装，可以直接在项目中应用，大大...
在Java编程语言中，字符串处理是一项常见的任务，特别是在解析、分析或操作文本数据时。本篇文章将深入探讨如何从字符串中提取括号内的内容...理解并熟练运用这些工具，能帮助开发者更有效地处理各种字符串相关的任务。
在这个函数中，我们首先检查子字符串是否超过父字符串长度，如果是，则不可能出现。然后，我们使用`IndexOf()`方法查找子字符串，并在找到后更新计数器并移动搜索的起始位置。这个过程会持续到找不到子字符串为止。 ...
无论是进行数据解析、日志分析，还是构建复杂的文本处理算法，有效的字符串操作都能极大地提高代码的效率和可读性。因此，花时间学习和实践这些基本技能是每个程序员职业生涯中不可或缺的一部分。
本文将详细介绍VB中对于字符串处理的基本方法及其一些实用的扩展技巧。 #### 1. 基本字符串操作 ##### 1.1 `Left`, `Right`, `Mid` 函数 - **`Left`**：返回字符串左侧指定数量的字符。 - 示例：`Left("Hello ...
字符串是不可变的数据类型，可以通过索引访问其字符，并且支持多种方法，如`Substring`用于提取子字符串，`Replace`用于替换特定字符或子串，以及`Split`用于分割字符串成字符串数组。在处理计算公式时，`Split`方法...
在实际应用中，我们可能还需要考虑一些边缘情况，例如当截取的长度超过了字符串的实际长度，这时通常会返回整个字符串或者空字符串，具体取决于编程语言的实现。 标签“字符串 截取”提示我们关注的重点在于理解和...