大致是:select a.aid,m.money,n.money
from A
inner join (select aid,sum(money) money from B where pro='A') m on a.aid=m.aid
inner join (select aid,sum(money) money from B where pro='B') n on a.aid=n.aid
from A
inner join (select aid,sum(money) money from B where pro='A') m on a.aid=m.aid
inner join (select aid,sum(money) money from B where pro='B') n on a.aid=n.aid
from A
left join
(select aid,sum(money) money from B where pro='A' group by aid) m on a.aid=m.aid
left join
(select aid,sum(money) money from B where pro='B' group by aid) n on a.aid=n.aid
insert #表A values(1,'张三','客户甲')
insert #表A values(2,'张三','客户乙')
insert #表A values(3,'张三','客户丙')
insert #表A values(4,'李四','客户丁')create table #表B(BID int,AId int,Pro varchar(10),[Money] int)
insert #表B values( 1 , 1 , 'A' , 123)
insert #表B values( 2 , 1 , 'B' , 456)
insert #表B values( 3 , 2 , 'A' , 789)
insert #表B values( 4 ,3 , 'B' , 101)
insert #表B values( 5 ,4, 'A' , 201)
declare @sql varchar(8000),@sql2 varchar(4000)
select @sql = 'select a.Name,a.Customername,',@sql2=''select @sql = @sql + 'sum(case b.Pro when '''+Pro+'''
then b.[Money] else null end) 产品'+Pro+',',@sql2=@sql2+' sum(产品'+Pro+'),'
from (select distinct Pro from #表B) as aset @sql = @sql + 'sum(money) 合计 into #temp from #表A a join #表B b on a.aid=b.aid group by a.Name,a.Customername ; select * from (select *,name tem1,1 tem2 from #temp union all select null name,''小计'' customername,'
set @sql=@sql+@sql2+'sum(合计),name,2 from #temp group by name ) mm order by tem1,tem2'exec(@sql)
godrop table #表A
drop table #表b