求一高效SQL语句,先谢了

[smneo]

俩个表

表A
aid, acontent
表B
bid,aid,btype,bcontent

A表和B表的关系是B表是A表的一个细节表.

表B的aid为关联的A表aid值,btype字段为:1,2,3,4四种类型.

一条表A记录最多和四条表B记录关联(分别为类型1,2,3,4的各一条),也可能一条不关联.就是表B没有相信的类型记录对应A表记录

例如
A表记录
1, abc
2, bcd

B表记录
1, 1, 1, ABC
2, 1, 2, BCD
3, 1, 3, CDE
4, 2, 4, EFG

需要联合查询到的结果为
aid,acontent,bid_1,bconten_1,bid_2,bconten_2,bid_3,bconten_3,bid_4,bconten_4,

bid_1,bid_2,bconten_1,bconten_2...分别为类型1,2,3,4的记录..

如上例中可能得到的结果就为
aid|acontent|bid_1 |bconten_1|bid_2 |bconten_2|bid_3|bconten_3|bid_4|bconten_4|
1 | abc | 1 | ABC | 2 | BCD | 3 | CDE | 空 | 空 |
2 | bcd | 空 | 空 | 空 | 空 | 空 | 空 | 4 | EFG |

这样的语句要怎么写呢?通用点的语句..

[dawugui]

select a.aid , a.acontent,
   max(case b.btype when 1 then btype else '空' end) bid_1,
   max(case b.btype when 1 then b.bcontent else '空' end) bconten_1,
   max(case b.btype when 2 then btype else '空' end) bid_2,
   max(case b.btype when 2 then b.bcontent else '空' end) bconten_2,
   max(case b.btype when 3 then btype else '空' end) bid_3,
   max(case b.btype when 3 then b.bcontent else '空' end) bconten_3,
   max(case b.btype when 4 then btype else '空' end) bid_4,
   max(case b.btype when 4 then b.bcontent else '空' end) bconten_4
from a ,b
where a.aid = b.aid
group by a.aid , a.acontent

[dawugui]

create table A(aid int, acontent varchar(10))
create table B(bid int,aid int,btype int,bcontent varchar(10))
insert into A values(1, 'abc') 
insert into A values(2, 'bcd')
insert into B values(1, 1, 1, 'ABC') 
insert into B values(2, 1, 2, 'BCD') 
insert into B values(3, 1, 3, 'CDE') 
insert into B values(4, 2, 4, 'EFG')
go

select a.aid , a.acontent,
   max(case b.btype when 1 then cast(b.btype as varchar) else '' end) bid_1,
   max(case b.btype when 1 then b.bcontent else '' end) bconten_1,
   max(case b.btype when 2 then cast(b.btype as varchar) else '' end) bid_2,
   max(case b.btype when 2 then b.bcontent else '' end) bconten_2,
   max(case b.btype when 3 then cast(b.btype as varchar) else '' end) bid_3,
   max(case b.btype when 3 then b.bcontent else '' end) bconten_3,
   max(case b.btype when 4 then cast(b.btype as varchar) else '' end) bid_4,
   max(case b.btype when 4 then b.bcontent else '' end) bconten_4
from a ,b
where a.aid = b.aid
group by a.aid , a.acontent

drop table a , b


/*
aid         acontent   bid_1                          bconten_1  bid_2                          bconten_2  bid_3                          bconten_3  bid_4                          bconten_4  
----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- 
1           abc        1                              ABC        2                              BCD        3                              CDE                                       
2           bcd                                                                                                                                      4                              EFG

（所影响的行数为 2 行）
*/

[smneo]

谢啦...