A表是主表，cd是主键
cd name
1 AA
2 BB
B表是从表，两个主键，cd，id
cd id money
1 1 100
1 2 200
1 3 300
2 1 1000
2 2 2000
2 3 3000
2 4 4000
2 5 5000
2 6 6000
....
要求拣出来的结果是
cd money1 money2 money3 money4 money5
1 100 200 300
2 1000 2000 3000 4000 5000
就是把B表变成一个横表，而且只取B表的5条数据，不够5条补空格，超过5条截去
希望高手帮忙啊。分还可以再加，谢谢！
------解决方案--------------------------------------------------------
create table a
(
cd int,
name char(8)
)
;
create table b
(
cd int,
id int,
money decimal(18,2)
)
;
insert into a values (1, 'AA '),(2, 'BB ');
insert into b values (1,1,100),(1,2,200),(1,3,300),(2,1,1000),(2,2,2000),(2,3,3000),(2,4,4000),(2,5,5000),(2,6,6000);
select a1.cd,(select money from b where a1.cd=b.cd and b.id=1),
(select COALESCE(money,0) from b where a1.cd=b.cd and b.id=2),
(select COALESCE(money,0) from b where a1.cd=b.cd and b.id=3),
(select COALESCE(money,0) from b where a1.cd=b.cd and b.id=4),
(select COALESCE(money,0) from b where a1.cd=b.cd and b.id=5)
from
(select distinct cd from b) as a1
order by cd
;
------解决方案--------------------------------------------------------
这样写应该也可以，只是B表的ID列一定要从1开始递增排列
select cd, sum(m1) as money1 , sum(m2) as money2 ,sum(m3) as money3 ,sum(m4) as money4 ,sum(m5) as money5
from (
select cd ,money as m1, 0.00 as m2 ,0.00 as m3 ,0.00 as m4 , 0.00 as m5
from b where id = 1
union
select cd , 0.00 as m1, money as m2 ,0.00 as m3 ,0.00 as m4 , 0.00 as m5
from b where id = 2
union
select cd , 0.00 as m1, 0.00 as m2 ,money as m3 ,0.00 as m4 , 0.00 as m5
from b where id = 3
union
select cd , 0.00 as m1, 0.00 as m2 ,0.00 as m3 ,money as m4 , 0.00 as m5