with cte as
(select a.bj,a.sfzh,a.xm,b.sfxn,b.sfje,c.jfje,
ROW_NUMBER()over(partition by b.sfzh order by sfxn) as rn from xstab as a join sftab as b
on a.sfzh=b.sfzh join qftab as c on b.sfzh=c.sfzh
and b.sfxn=c.sfxn)
select a.bj,a.sfzh,a.xm,
sum(case when rn=1 then sfje else 0 end )as 第一学年收费金额,
sum(case when rn=1 then qfje else 0 end )as 第一学年欠费金额,
sum(case when rn=2 then sfje else 0 end )as 第二学年收费金额,
sum(case when rn=2 then sfje else 0 end )as 第二学年欠费金额,
sum(case when rn=3 then sfje else 0 end )as 第三学年收费金额,
sum(case when rn=3 then sfje else 0 end )as 第三学年欠费金额 from cte 试试这个
(select a.bj,a.sfzh,a.xm,b.sfxn,b.sfje,c.jfje,
ROW_NUMBER()over(partition by b.sfzh order by sfxn) as rn from xstab as a join sftab as b
on a.sfzh=b.sfzh join qftab as c on b.sfzh=c.sfzh
and b.sfxn=c.sfxn)
select a.bj,a.sfzh,a.xm,
sum(case when rn=1 then sfje else 0 end )as 第一学年收费金额,
sum(case when rn=1 then qfje else 0 end )as 第一学年欠费金额,
sum(case when rn=2 then sfje else 0 end )as 第二学年收费金额,
sum(case when rn=2 then sfje else 0 end )as 第二学年欠费金额,
sum(case when rn=3 then sfje else 0 end )as 第三学年收费金额,
sum(case when rn=3 then sfje else 0 end )as 第三学年欠费金额 from cte 试试这个
with cte as
(select a.bj,a.sfzh,a.xm,b.sfxn,b.sfje,c.jfje,
ROW_NUMBER()over(partition by b.sfzh order by sfxn) as rn from xstab as a join sftab as b
on a.sfzh=b.sfzh join qftab as c on b.sfzh=c.sfzh
and b.sfxn=c.sfxn)
select bj,sfzh,xm,
sum(case when rn=1 then sfje else 0 end )as 第一学年收费金额,
sum(case when rn=1 then qfje else 0 end )as 第一学年欠费金额,
sum(case when rn=2 then sfje else 0 end )as 第二学年收费金额,
sum(case when rn=2 then sfje else 0 end )as 第二学年欠费金额,
sum(case when rn=3 then sfje else 0 end )as 第三学年收费金额,
sum(case when rn=3 then sfje else 0 end )as 第三学年欠费金额 from cte
12级2班 530110017 潘文 2012 800 2000 1
12级2班 530110017 潘文 2012 900 2000 2
12级2班 530110017 潘文 2012 3400 2000 3
12级2班 530110017 潘文 2012 20 2000 4
12级2班 530110017 潘文 2012 20 2000 5
12级2班 530110017 潘文 2012 80 2000 6
12级2班 530110017 潘文 2012 80 2000 7
12级2班 530110017 潘文 2012 100 2000 8
12级2班 530110017 潘文 2012 500 2000 9
12级2班 530110017 潘文 2012 600 2000 10
12级2班 530110017 潘文 2013 600 800 11
12级2班 530110017 潘文 2013 500 800 12
12级2班 530110017 潘文 2013 100 800 13
12级2班 530110017 潘文 2013 80 800 14
12级2班 530110017 潘文 2013 20 800 15
12级2班 530110017 潘文 2013 1300 800 16
12级2班 530110017 潘文 2013 5400 800 17后面的那段select语句还是有问题,在SQL SERVER2005下执行。