CREATE PROCEDURE TRUNC AS
declare @dt varchar(10)
declare @sql varchar(3000)
declare @sql1 nvarchar(3000)
declare @sql2 nvarchar(3000)declare cr CURSOR for select distinct rq from yy order by rq
OPEN cr
FETCH NEXT FROM cr INTO @dt --****
set @sql=''WHILE @@FETCH_STATUS = 0
BEGIN set @sql1=@sql1+'sum((case when rq='''+@dt+''' then quantity else 0 end)) as '''+@dt+''','
set @sql2=@sql2+'sum(a.['+@dt+']),'
FETCH NEXT FROM cr INTO @dt --****
ENDset @sql='select customer,'+@sql1+'sum(quantity) as ''合计'' into ti from yy group by customer'
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) into ti from (' +@sql+') as a order by customer'exec(@sql)CLOSE cr
DEALLOCATE cr
GO
declare @dt varchar(10)
declare @sql varchar(3000)
declare @sql1 nvarchar(3000)
declare @sql2 nvarchar(3000)declare cr CURSOR for select distinct rq from yy order by rq
OPEN cr
FETCH NEXT FROM cr INTO @dt --****
set @sql=''WHILE @@FETCH_STATUS = 0
BEGIN set @sql1=@sql1+'sum((case when rq='''+@dt+''' then quantity else 0 end)) as '''+@dt+''','
set @sql2=@sql2+'sum(a.['+@dt+']),'
FETCH NEXT FROM cr INTO @dt --****
ENDset @sql='select customer,'+@sql1+'sum(quantity) as ''合计'' into ti from yy group by customer'
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) into ti from (' +@sql+') as a order by customer'exec(@sql)CLOSE cr
DEALLOCATE cr
GO
解决方案 »
- 问下SQLserver 如何根据参数是否未空 来添加where条件
- sql2005打开表结构出现提示组件发生了无法处理的异常
- 解决立即结帖!!!!!在线等待!很简单的问题:在B表上怎么插入A表所有满足WHERE条件的记录?
- 有两个字段唯一确定表里是否有那些记录,如果没有就插入这些记录,要怎样做???
- 一个复杂的编号:怎么用SQL语句来编序号
- MSSQL 触发器的问题。
- 大家帮助我好吗,很急...................
- 这样一句SQL怎样写?
- 在sql server中计算列是和概念???(庸手可以skip,高手请解答)
- 烦请高手pengdali(大力)解决此问题
- 关于sql server服务器注册的问题!
- SOS!!Sql Server 卸載後無法重新安裝,這裡是日誌,麻煩個偉大哥幫忙.n次失敗~~~(安裝程式無法設定伺服器。詳細資訊請參考伺服器錯誤記錄檔
为何要这样改,我的做法错在哪里
刚打开时是在第一条记录的前面。
第一个FETCH NEXT 到第一条记录后面那个就是循环用了
要用FETCH NEXT 读取游标,使它处于结果集第一行,@@FETCH_STATUS=0
所以
你的WHILE @@FETCH_STATUS=0 就不执行循环了,因为@@FETCH_STATUS=-1
还是不行,不能生成表ti,但也没有报错
在exec(@sql)前加print @sql 看错在那?关把值贴出来!
另一个@sql2=@sql2+'sum(a.['+@dt+']),' 这个@dt恐怕要用convert转换成年月日的跟你表中字段相同的格式,不然可能会是"2002-02-02 00:00:00.000"
在set @sql=''
后面加上:
set @sql1=''
set @sql2=''
declare @dt varchar(10)
declare @sql varchar(3000)
declare @sql1 nvarchar(3000)
declare @sql2 nvarchar(3000)declare cr CURSOR for select distinct rq from yy order by rq
OPEN cr
FETCH NEXT FROM cr INTO @dt --****
set @sql=''
set @sql1='' --***
set @sql2='' --***
WHILE @@FETCH_STATUS = 0
BEGIN set @sql1=@sql1+'sum((case when rq='''+@dt+''' then quantity else 0 end)) as '''+@dt+''','
set @sql2=@sql2+'sum(a.['+convert(varchar(10),@dt,120)+']),' --*
FETCH NEXT FROM cr INTO @dt --****
ENDset @sql='select customer,'+@sql1+'sum(quantity) as ''合计'' into ti from yy group by customer'
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) into ti from (' +@sql+') as a order by customer'exec(@sql)CLOSE cr
DEALLOCATE cr
GO
日期2002-2-1变成02 1 2002 ,应该是类型转换错误,应怎样改,日期转换成字符串好象应该是这样
SELECT INTO 必须是包含 UNION 运算符的 SQL 语句中的第一个查询。
有union就不能有into,这个问题有没有的解决,只差这个问题。
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) into ti from (' +@sql+') as a order by customer'-->set @sql='select customer,'+@sql1+'sum(quantity) as ''合计'' into ti from yy group by customer'
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) from (' +@sql+') as a order by customer'
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) from (' +@sql+') as a order by customer'
我试过了的,它的功能是先UNION 后新成一个TI表.