仅供体会,无商业用途 (^_^)
---------------------------------------------------view-->vt_cysKh-----------------------------------------------------
if exists (select * from sysobjects where name = 'vt_cysKh')
drop view vt_cysKh
go
create view vt_cysKh
as
select n.cysmc,m.sum_price,m.khtype,m.khDate
from
(
select tk.cysbm,sum(tk.xmTimes*(cast(kk.money as int))) as sum_price, kk.khtype,tk.khDate
from T_kfxmpf tk inner join kkxm kk on (tk.xmid =kk.id)
where state=3 group by tk.cysbm,kk.khtype,tk.khDate,tk.khDate
) as m, t_cysxx as n
where m.cysbm=n.cysbm
go
----------------------------------------procedure-->proc_fetchCysReport-------------------------------------------
if exists (select * from sysobjects where name = 'proc_fetchCysReport')
drop procedure proc_fetchCysReport
go
create procedure proc_fetchCysReport
@startDate varchar(16),
@endDate varchar(16)
as
declare cur cursor for select distinct khtype from (select n.cysmc,m.khtype from (select tk.cysbm, kk.khtype from T_kfxmpf tk inner join kkxm kk on (tk.xmid =kk.id) where state=3) as m, t_cysxx as n where m.cysbm=n.cysbm) as s order by khtype asc
declare
@khtype varchar(20),
@sum varchar(300),
@case varchar(500),
@sql nvarchar(1000)
set @sum = ''
set @case = ''
set @sql = ''
if @startDate = ''
select @startDate = min(tk.khDate) from T_kfxmpf tk inner join kkxm kk on (tk.xmid =kk.id) where state=3
if @endDate = ''
select @endDate = max(tk.khDate) from T_kfxmpf tk inner join kkxm kk on (tk.xmid =kk.id) where state=3
open cur
fetch next from cur into @khtype
while @@fetch_status =0
begin
set @sum = @sum + 'sum(' + @khtype + ') as ' + @khtype +','
set @case = @case + '(case khtype when ''' + @khtype + ''' then isnull(sum_price,0) end) as ' + @khtype +','
fetch next from cur into @khtype
end
close cur
deallocate cur
if right(@case,1) = ','
set @case = stuff(@case, len(@case), 1, ' from vt_cysKh where khDate between ''' + @startDate + ''' and ''' + @endDate +'''')
if right(@sum,1) = ','
set @sum = stuff(@sum,len(@sum),1,'')
set @sql = ' select cysmc, ' + @sum + ' from ( select cysmc, ' + @case + ') as b group by cysmc'
execute sp_executesql @sql
select distinct b.khtype,a.sp
from (select khtype,sum(sum_price) as sp from vt_cysKh where khDate between @startDate and @endDate group by khtype) as a
right outer join vt_cysKh as b on a.khtype = b.khtype
go
/*
select * from vt_cysKh
execute proc_fetchCysReport '2010-10-01','2011-02-14'
*/