A表:
memberNo cardno
100001 1001
100002 1002
100003 1003
B表：
cardno pay_money operatorid
1001 1000 001
1002 1000 002
1003 1000 001
C表：
operatorid username
001 张三
002 李四
得到结果：
卡号 会员号 交款
1001 100001 1000
1003 100003 1000
操作员 张三 2张 合计 2000
1002 100002 1000
操作员 李四 1张 合计 1000
合计张数 2张 合计金额 3000
------解决方案--------------------
create proc getmyrecord
(@tmptbname varchar(20))
as
declare @sqlstr nvarchar(2000)
declare @a int
set @a=0
set @sqlstr= ' if exists (select * from sysobjects where name= ' ' '[email protected]+ ' ' ')
drop table '[email protected]
exec(@sqlstr)
if @@error <> 0 goto err_a
set @sqlstr= 'select space(20) as t1,space(20) as t2,space(20) as t3 ,space(20) as t4 , space(20) as t5 into '[email protected]+ ' where 1=2 '
exec(@sqlstr)
if @@error <> 0 goto err_a
declare @operatorid varchar(20)
declare cur cursor for select distinct operatorid from C表 order by operatorid
open cur
fetch next from cur into @operatorid
while @@fetch_status=0
begin
if @a <> 1
begin
set @sqlstr= 'insert into '[email protected]+ '
select ' '卡号 ' ', ' '会员号 ' ', ' '交款 ' ', ' ' ' ', ' ' ' ' '
exec(@sqlstr)
if @@error <> 0 goto err_a
set @a=1
end
set @sqlstr= 'insert into '[email protected]+
' select a.cardno,isnull(b.memberNo, ' ' ' '),a.pay_money, ' ' ' ', ' ' ' ' from B表 a left join A表 b on a.cardno=b.cardno
where a.operatorid= ' ' '[email protected]+ ' ' ' '
print @sqlstr
exec(@sqlstr)
if @@error <> 0 goto err_a
set @sqlstr= 'insert into '[email protected]+
' select ' '操作员 ' ',a.username,rtrim(cast(count(*) as varchar(8)))+ ' '张 ' ', ' '合计 ' ',sum(isnull(pay_money,0))
from C表 a left join B表 b on a.operatorid=b.operatorid
where a.operatorid= ' ' '[email protected]+ ' ' ' group by a.username '
print @sqlstr
exec(@sqlstr)
if @@error <> 0 goto err_a
fetch next from cur into @operatorid
end
close cur
deallocate cur
set @sqlstr= 'insert into '[email protected]+ ' select ' ' ' ', ' '合计张数 ' ',rtrim(cast(count(*) as varchar(8)))+ ' '张 ' ', ' '合计金额 ' ',sum(isnull(pay_money,0)) from B表 '