请看下面的存储过程:
CREATE PROCEDURE test2
@jyrqi datetime --输入日期作为存储过程的参数
as
if object_id('temp..#t') is not null
drop table #t
create table #t
(
idno varchar(15),
dept varchar(6),
lbmc varchar(10),
jyrqi datetime,
jyl decimal(9,2)
)
insert into #t(idno,dept,lbmc,jyrqi,jyl) select idno,dept,lbmc,jyrqi,jyl from oil_record
where (dept='箱管部')
and( datediff(mm,jyrqi,@jyrqi)=0 and datediff(yyyy,jyrqi,@jyrqi)=0)
declare @sql varchar(5000)
set @sql='select idno,'
select @sql=@sql+'sum(case datepart(dd,jyrqi) when '+ltrim(number)+' then jyl else 0 end) as n'+ltrim(number)+','
from master..spt_values where type='p' and number between 1 and 31
set @sql=@sql+'sum(jyl) as row_total from #t where datediff(mm,jyrqi,'''+convert(varchar(10),@jyrqi,120)+''')=0 group by idno
union all select ''合计'', '
select @sql=@sql+'sum(case datepart(dd,jyrqi) when '+ltrim(number)+' then jyl else 0 end) as n'+ltrim(number)+','
from master..spt_values where type='p' and number between 1 and 31
set @sql=@sql+'sum(jyl) as row_total from #t where datediff(mm,jyrqi,'''+convert(varchar(10),@jyrqi,120)+''')=0 '
--print @sql
exec(@sql)
GO执行后的结果见截图:
http://photo.163.com/luck0790/big/#aid=197694854&id=5898091620
想在这个结果中加入start_time和end_time两列(decimal(9,2)类型),这两列的值来自另一个表(b)。要求#t.idno =b.idno #t.jyrqi=b.jyrqi_gs
CREATE PROCEDURE test2
@jyrqi datetime --输入日期作为存储过程的参数
as
if object_id('temp..#t') is not null
drop table #t
create table #t
(
idno varchar(15),
dept varchar(6),
lbmc varchar(10),
jyrqi datetime,
jyl decimal(9,2)
)
insert into #t(idno,dept,lbmc,jyrqi,jyl) select idno,dept,lbmc,jyrqi,jyl from oil_record
where (dept='箱管部')
and( datediff(mm,jyrqi,@jyrqi)=0 and datediff(yyyy,jyrqi,@jyrqi)=0)
declare @sql varchar(5000)
set @sql='select idno,'
select @sql=@sql+'sum(case datepart(dd,jyrqi) when '+ltrim(number)+' then jyl else 0 end) as n'+ltrim(number)+','
from master..spt_values where type='p' and number between 1 and 31
set @sql=@sql+'sum(jyl) as row_total from #t where datediff(mm,jyrqi,'''+convert(varchar(10),@jyrqi,120)+''')=0 group by idno
union all select ''合计'', '
select @sql=@sql+'sum(case datepart(dd,jyrqi) when '+ltrim(number)+' then jyl else 0 end) as n'+ltrim(number)+','
from master..spt_values where type='p' and number between 1 and 31
set @sql=@sql+'sum(jyl) as row_total from #t where datediff(mm,jyrqi,'''+convert(varchar(10),@jyrqi,120)+''')=0 '
--print @sql
exec(@sql)
GO执行后的结果见截图:
http://photo.163.com/luck0790/big/#aid=197694854&id=5898091620
想在这个结果中加入start_time和end_time两列(decimal(9,2)类型),这两列的值来自另一个表(b)。要求#t.idno =b.idno #t.jyrqi=b.jyrqi_gs
能详细说看看吗?有点不理解你说的.原来是想在临时表(#T)中加入start_time和end_time可是不行,start_time和end_time对应IDNO只有一条,如果加入的话同个IDNO会有多个start_time和end_time值
CREATE PROCEDURE test2
@jyrqi datetime --输入日期作为存储过程的参数
as
if object_id('temp..#t') is not null
drop table #t
create table #t
(
idno varchar(15),
dept varchar(6),
lbmc varchar(10),
jyrqi datetime,
jyl decimal(9,2)
)
insert into #t(idno,dept,lbmc,jyrqi,jyl) select idno,dept,lbmc,jyrqi,jyl from oil_record
where (dept='箱管部')
and( datediff(mm,jyrqi,@jyrqi)=0 and datediff(yyyy,jyrqi,@jyrqi)=0)
declare @sql varchar(5000)
set @sql='select b.start_time,b.end_time, idno,'
select @sql=@sql+'sum(case datepart(dd,jyrqi) when '+ltrim(number)+' then jyl else 0 end) as n'+ltrim(number)+','
from master..spt_values where type='p' and number between 1 and 31
set @sql=@sql+'sum(jyl) as row_total from #t where datediff(mm,jyrqi,'''+convert(varchar(10),@jyrqi,120)+''')=0 group by idno
union all select ''合计'', '
select @sql=@sql+'sum(case datepart(dd,jyrqi) when '+ltrim(number)+' then jyl else 0 end) as n'+ltrim(number)+','
from master..spt_values where type='p' and number between 1 and 31
set @sql=@sql+'sum(jyl) as row_total from #t,b where datediff(mm,jyrqi,'''+convert(varchar(10),@jyrqi,120)+''')=0 and #t.idno =b.idno and #t.jyrqi=b.jyrqi_gs'
--print @sql
exec(@sql)
GO
对象名 'b' 无效。
服务器: 消息 107,级别 16,状态 1,行 1
列前缀 'b' 与查询中所用的表名或别名不匹配。
服务器: 消息 107,级别 16,状态 1,行 1
列前缀 'b' 与查询中所用的表名或别名不匹配。