一行 多个分组

create table #t
(
    id int,
    bankno nvarchar(10),
    amount decimal
)

select '1' filed1,'a' field2 into #t2

insert into #t(id,bankno,amount)values(1,'a',1)
insert into #t(id,bankno,amount)values(1,'a',2)
insert into #t(id,bankno,amount)values(1,'a',3)
insert into #t(id,bankno,amount)values(1,'b',1)
insert into #t(id,bankno,amount)values(1,'b',4)
insert into #t(id,bankno,amount)values(1,'b',3)
insert into #t(id,bankno,amount)values(2,'b',1)
insert into #t(id,bankno,amount)values(2,'a',5)
insert into #t(id,bankno,amount)values(2,'a',3)


select * from #t a
left join #t2 b on a.id=b.filed1 and a.bankno=b.field2

select a.id,count(1) 期数,sum(case when b.filed1 is not null then amount else 0 end) 服务费 from #t a
left join #t2 b on a.id=b.filed1 and a.bankno=b.field2
group by a.id


drop table #t
drop table #t2