高手帮忙，求一select语句
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
from b where id = 4
union
select cd , 0.00 as m1, 0.00 as m2 ,0.00 as m3 ,0.00 as m4 , money as m5
from b where id = 5 ) as a
group by cd;
[解决办法]
select cd,
(select money from b where cd=a.cd and id=1),
(select money from b where cd=a.cd and id=2),
(select money from b where cd=a.cd and id=3),
(select money from b where cd=a.cd and id=4),
(select money from b where cd=a.cd and id=5)
from a
order by cd
;
[解决办法]
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 '),(4, 'CC ');
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 a.CD,
sum((Case B.ID when 1 then B.Money else 0 end )) Money1,
sum((Case B.ID when 2 then B.Money else 0 end )) Money2,
sum((Case B.ID when 3 then B.Money else 0 end )) Money3,
sum((Case B.ID when 4 then B.Money else 0 end )) Money4,
sum((Case B.ID when 5 then B.Money else 0 end )) Money5
From a left join b
on a.CD=b.CD
Group by a.CD
CD NAME MONEY1 MONEY2 MONEY3 MONEY4 MONEY5
----- ------- --------- --------- --------- --------- ---------
1 AA 100.00 200.00 300.00 0.00 0.00
2 BB 1000.00 2000.00 3000.00 4000.00 5000.00
4 CC 0.00 0.00 0.00 0.00 0.00
3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 94/ms]
[Executed: 07-3-9 下午11时13分13秒 ] [Execution: 78/ms]
[解决办法]
或者 楼主不是想要 a 表的全部，只是b 表的全部种类
select a.CD,
sum((Case B.ID when 1 then B.Money else 0 end )) Money1,
sum((Case B.ID when 2 then B.Money else 0 end )) Money2,
sum((Case B.ID when 3 then B.Money else 0 end )) Money3,
sum((Case B.ID when 4 then B.Money else 0 end )) Money4,
sum((Case B.ID when 5 then B.Money else 0 end )) Money5
From a , b
where a.CD=b.CD
Group by a.CD
经过验证，这种方式检索性能最好。。
