求一个复杂的查询(一个主表和两个从表）
设计三个测试表：AAA表为主表，BBB表为AAA从表，通过AAA.AID=BBB.AID关联，CCC表也为AAA表从表，通过AAA.AID = CCC.AID关联：
create table AAA
([AID] [int] identity(1,1),aname [varchar](50))
on [primary]
insert into AAA (aname) values ( 'A1 ')
insert into AAA (aname) values ( 'A2 ')
insert into AAA (aname) values ( 'A3 ')
insert into AAA (aname) values ( 'A4 ')
insert into AAA (aname) values ( 'A5 ')
create table BBB
([BID] [int] identity(1,1),AID int,Bname [varchar](50))
on [primary]
insert into BBB (AID,Bname) values (1, 'BBNAME1 ')
insert into BBB (AID,Bname) values (1, 'BBNAME2 ')
insert into BBB (AID,Bname) values (3, 'BBNAME3 ')
create table CCC
([CID] [int] identity(1,1),AID int,Cname [varchar](50))
on [primary]
insert into CCC (AID,Cname) values (1, 'CCNAME1 ')
insert into CCC (AID,Cname) values (2, 'CCNAME2 ')
insert into CCC (AID,Cname) values (3, 'CCNAME3 ')
insert into CCC (AID,Cname) values (3, 'CCNAME4 ')
insert into CCC (AID,Cname) values (4, 'CCNAME5 ')
insert into CCC (AID,Cname) values (3, 'CCNAME6 ')
现在我想查出这三个表得记录，对于BBB表，如果AID有重复的，就按BID的降序取Bname第一条记录，若AID不存在，Bname就为空,CCC表同理。
能过上面语句，分别得出AAA和BBB和CCC的数据如下：
AAA: AID aname
1 A1
2 A2
3 A3
4 A4
5 A5
BBB: BID AID Bname
1 1BBNAME1
2 1BBNAME2
3 3BBNAME3
CCC: CID AID Cname
1 1CCNAME1
2 2CCNAME2
3 3CCNAME3
4 3CCNAME4
5 4CCNAME5
6 3CCNAME6
我想得出下面的结果：
AID aname Bname Cname
1 A1 BBNAME2 CCNAME1
2 A2 NULL CCNAME2
3 A3 BBNAME3 CCNAME6
4 A4 NULL CCNAME5
5 A5 NULL NULL
SQL在存储过程里怎么写，请高手帮忙！ thinks
[解决办法]
select aaa.aid,aaa.aname,bbb.bname,ccc.cname
from aaa left join
(select * from bbb b where not exists(select 1 from bbb where aid = b.aid and bid > a.bid) bbb on aaa.aid = bbb.aid
left join
(select * from ccc c where not exists(select 1 from ccc where aid = c.aid and cid > a.cid) ccc on aaa.aid = ccc.aid
[解决办法]
create table AAA
([AID] [int] identity(1,1),aname [varchar](50))
on [primary]
insert into AAA (aname) values ( 'A1 ')
insert into AAA (aname) values ( 'A2 ')
insert into AAA (aname) values ( 'A3 ')
insert into AAA (aname) values ( 'A4 ')
insert into AAA (aname) values ( 'A5 ')
create table BBB
([BID] [int] identity(1,1),AID int,Bname [varchar](50))
on [primary]
insert into BBB (AID,Bname) values (1, 'BBNAME1 ')
insert into BBB (AID,Bname) values (1, 'BBNAME2 ')
insert into BBB (AID,Bname) values (3, 'BBNAME3 ')
create table CCC
([CID] [int] identity(1,1),AID int,Cname [varchar](50))
on [primary]
insert into CCC (AID,Cname) values (1, 'CCNAME1 ')
insert into CCC (AID,Cname) values (2, 'CCNAME2 ')
insert into CCC (AID,Cname) values (3, 'CCNAME3 ')
insert into CCC (AID,Cname) values (3, 'CCNAME4 ')
insert into CCC (AID,Cname) values (4, 'CCNAME5 ')
insert into CCC (AID,Cname) values (3, 'CCNAME6 ')
select AAA.AID, AAA.aname, BBB.Bname, CCC.Cname from
(
select AAA.*, BBB.BID, CCC.CID from AAA
left join
(select BID=max(BID), AID from BBB group by AID) BBB on AAA.AID=BBB.AID
left join
(select CID=max(CID), AID from CCC group by AID) CCC on AAA.AID=CCC.AID
)AAA
left join BBB on AAA.BID=BBB.BID
left join CCC on AAA.CID=CCC.CID
--result
AID aname Bname Cname
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 A1 BBNAME2 CCNAME1
2 A2 NULL CCNAME2
3 A3 BBNAME3 CCNAME6
4 A4 NULL CCNAME5
5 A5 NULL NULL
(5 row(s) affected)
