动态查询统计的问题...语句超出限制长度.仍未解决,急...

[xiaoyuehen]

以下是我自定义的存储过程, 采用动态语句查询统计...

CREATE PROCEDURE dbo.u_dt_tl_msdqfbtj
--通路门市地区分布统计
/*
通路 1 2.....12 合 计
好又多 T1 T2 T12 TB-TT
家乐 C1 C2 Cn TC-TT
. . . . .
. . . . .
. . . . .

*/
AS
SELECT msdm, lsdq AS dqdm, tlmc INTO #t FROM hcszhmc WHERE tlmc IS NOT NULL

--通路门市地区分布统计
----------------------------------------------------------------------------------
--/*
DECLARE @SQL VARCHAR(8000)
DECLARE @tsql VARCHAR(8000)
SET @sql = 'select dqdm as ''地区'''
--生成动态列
SELECT @sql = @sql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM'
PRINT @SQL
SET @tsql = ' UNION SELECT ''合计'''
--生成动态列
SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'

--SET @sql = @sql + @tsql
EXEC(@sql)
--*/
----------------------------------------------------------------------------------

go

在 通路(tlmc) (用于指示某家门市属于某个类别) 比较少的情况下...@sql 语句不会超出 3999, 存储过程得已正确执行..现在 通路(tlmc) 类别多于 80 几个的时候, @sql 字符串被截断掉, 导致 sql 语句不完整, 无法执行...:(

我已经有发过一贴来讨论, 地址在
http://expert.csdn.net/Expert/TopicView1.asp?id=2295919

如果我用Delphi中动态生成, 势必加重编写工作量, 另外, 在提交到服务器时, 可能还要进行 UTF-8 编码转换, 这点我又还没掌握..

所以, 我想请大虾帮我想想看能不能用 SQL 中的 ntext 数据类型来替代, 考虑到将来可能会增加到 100 个通路以上..8000 的字符可能也不够用 :(

[aierong]

用sp_executesql处理
支持参数替换

在批处理、名称作用域和数据库上下文方面，sp_executesql 与 EXECUTE 的行为相同。

[tyd163xx]

可以多定义几个变量@SQL1，@SQL2，@SQL3……，在游标里面组织字符串，LEN(@SQL1)>7500时用@SQL2……，执行的时候用EXEC(@SQL1，@SQL2，@SQL3……+@TSQL)

[perwang]

严重同意 tyd163xx的方法，不过要想程序好用，还是在前端处理多列的好

一般后端进行分组group by ，返回小结果集，前端再展开成多列，这样速度就没有问题了

[xiaoyuehen]

暂时以另一种方式解决:横向显示 地区(dqdm), 纵向显示 通路(tlmc)..所幸 地区 分类在50个以下,所以 @sql 字符串将在 2000 以下, 不会超出限制, 存储过程得以顺利执行..另外, 地区 分类的增加比较缓慢..所以几年内应不会出现错误吧..:)
现在的存储过程修改如下:

CREATE PROCEDURE dbo.u_dt_tl_msdqfbtj
--通路门市地区分布统计
/*
通路 TB TBC.....TZN 合 计
好又多 T1 T2 T12 TT
家乐 C1 C2 Cn TT
. . . . .
. . . . .
. . . . .

*/
AS
SELECT msdm, RTRIM(lsdq) AS dqdm, RTRIM(tlmc) AS tlmc INTO #t FROM hcszhmc WHERE tlmc IS NOT NULL

--通路门市地区分布统计
----------------------------------------------------------------------------------
--/*
DECLARE @SQL VARCHAR(8000)
DECLARE @tsql VARCHAR(8000)
SET @sql = 'select tlmc as ''通路名称'''
--生成动态列
SELECT @sql = @sql + ', SUM(CASE dqdm WHEN ''' + CAST(dqdm AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + RTRIM(CAST(dqdm AS NVARCHAR(30))) + ']' FROM (SELECT DISTINCT dqdm FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t WHERE tlmc IS NOT NULL AND tlmc<>'''' GROUP BY tlmc'
SET @tsql = ' UNION ALL SELECT ''合计'''
--生成动态列
SELECT @tsql = @tsql + ', SUM(CASE DQDM WHEN ''' + CAST(dqdm AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(dqdm AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT dqdm FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t WHERE tlmc IS NOT NULL AND tlmc<>'''''

SET @sql = @sql + @tsql
EXEC(@sql)
--*/
----------------------------------------------------------------------------------

go


谢谢楼上朋友的回复!

1.sp_executesql 我还没有用过, 所以, 我不知从何下手..

2.tyd163xx(答案) 和 perwang(阿吉) 的意见不错..我也有想过, 只是我现在对游标也不太熟,可以给个较详细的例子吗?

[zjcxc]

用这个吧,比较通用

DECLARE @SQL1 VARCHAR(8000),@sql2 varchar(8000) --定义足够多的变量
declare @tsql varchar(8000)

declare @fdcount int
set @fdcount=50 --定义厉大慨多少个值可以在一个字符串变量中存储

--生成动态列
--生成数据处理临时表
select id=identity(int,0,1),0 as groupid,0 as sortid,*
into #tb from(select DISTINCT TLMC FROM #t) a

--对要处理的字段分组
update #tb set groupid=id/@fdcount,sortid=id % @fdcount

--组合字符串
select @fdcount=@fdcount-1
,@sql1=''
,@sql2='' --如果有更多的变量,则继续写下去

while @fdcount>=0
begin
select @sql1=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sql
FROM #tb where sortid=@fdcount and groupid=0

select @sql2=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sq2
FROM #tb where sortid=@fdcount and groupid=1
--如果有更多的变量,继续写下去

set @fdcount=@fdcount-1
end

--生成动态列
SET @tsql = ' UNION SELECT ''合计'''
SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'

--查询结果
exec('select 地区=dqdm'+@sql1+@sql2 --如果有更多的变量,继续写下去
+', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM'
+@tsql)

[liufh520]

兄弟改天帮你

[zjcxc]

http://expert.csdn.net/Expert/topic/2303/2303308.xml?temp=.5107233

[JYYF21]

DECLARE @SQL1 VARCHAR(8000),@sql2 varchar(8000) --定义足够多的变量
declare @tsql varchar(8000)

declare @fdcount int
set @fdcount=50 --定义厉大慨多少个值可以在一个字符串变量中存储

--生成动态列
--生成数据处理临时表
select id=identity(int,0,1),0 as groupid,0 as sortid,*
into #tb from(select DISTINCT TLMC FROM #t) a

--对要处理的字段分组
update #tb set groupid=id/@fdcount,sortid=id % @fdcount

--组合字符串
select @fdcount=@fdcount-1
,@sql1=''
,@sql2='' --如果有更多的变量,则继续写下去

while @fdcount>=0
begin
select @sql1=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sql
FROM #tb where sortid=@fdcount and groupid=0

select @sql2=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sq2
FROM #tb where sortid=@fdcount and groupid=1
--如果有更多的变量,继续写下去

set @fdcount=@fdcount-1
end

--生成动态列
SET @tsql = ' UNION SELECT ''合计'''
SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'

--查询结果
exec('select 地区=dqdm'+@sql1+@sql2 --如果有更多的变量,继续写下去
+', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM'
+@tsql)