主子表关联查询问题!

[wym93519924]

主表(ptable)字段（bid,bname,btype）,其中bid为主键
主表中有以下记录：
bid bname btype
--------------------------
b1001 名称1 aaa
b1002 名称2 bbb
b1003 名称3 ccc
b1004 名称4 ddd
b1005 名称5 eee

子表(dtable)字段（did,bid,dcontent,dnumber）,其中did为主键，bid为外键，关联主表主键
子表中有以下记录：
did bid dcontent dnumber
---------------------------------------
d1001 b1001 1111111111 1.11
d1002 b1001 2222222222 1.55
d1003 b1001 3333333333 1.25
d1004 b1002 4444444444 6.4
d1005 b1002 5555555555 10.5

想得到的结果：
bid bname btype did dcontent dnumber
---------------------------------------------------------
b1001 名称1 aaa d1002 2222222222 1.55
b1002 名称2 bbb d1005 5555555555 10.5
b1003 名称3 ccc NULL NULL NULL
b1004 名称4 ddd NULL NULL NULL
b1005 名称5 eee NULL NULL NULL

说明：即通过主表关联查出子表中dnumber值最大的记录与主表匹配，请教大侠们怎么用一条SQL语句构造出来，烦劳之处不尽感谢谢！

[青锋-SS]

select p.*,d.did,d.dcontent,d.number
from ptable p left join (select * from dtable t where not exists(select 1 from dtable where bid=t.bid and dnumber>t.dnumber) d
where p.bid=d.bid

[青锋-SS]

select p.*,d.did,d.dcontent,d.number
from ptable p left join (select * from dtable t where not exists(select 1 from dtable where bid=t.bid and dnumber>t.dnumber) d
on p.bid=d.bid

[青锋-SS]

1楼的写错了,修改成2楼.

[dobear_0922]

select bid, bname, btype, did, dcontent, dnumber
from ptable p 
left join (select * from dtable t where not exists(select 1 from dtable where did=t.did and dnumber>t.dnumber)) d
on p.bid=d.did

[dobear_0922]

婶婶速度真快呀，呵呵

[dawugui]

select m.* , n.did,n.dcontent,n.dnumber from ptable m left join
(select * from dtable t where dnumber = (select max(dnumber) from dtable where bid = t.bid)) n
on m.bid = n.bid

[青锋-SS]

create table ptable(bid varchar(10),bname varchar(20),btype varchar(10))
go
insert into ptable select 'b1001','名称1','aaa'
insert into ptable select 'b1002','名称2','bbb'
insert into ptable select 'b1003','名称3','ccc'
insert into ptable select 'b1004','名称4','ddd'
insert into ptable select 'b1005','名称5','eee'
go
create table dtable(did varchar(10),bid varchar(10),dcontent varchar(20),dnumber decimal(18,2))
go
insert into dtable select 'd1001','b1001','1111111111',1.11
insert into dtable select 'd1002','b1001','2222222222',1.55
insert into dtable select 'd1003','b1001','3333333333',1.25
insert into dtable select 'd1004','b1002','4444444444',6.4
insert into dtable select 'd1005','b1002','5555555555',10.5
go
select p.*,d.did,d.dcontent,d.dnumber
from ptable p left join (select * from dtable t where not exists(select 1 from dtable where bid=t.bid and dnumber>t.dnumber)) d
on p.bid=d.bid
go
drop table ptable,dtable
go
/*结果
bid        bname                btype      did        dcontent             dnumber              
---------- -------------------- ---------- ---------- -------------------- -------------------- 
b1001      名称1                  aaa        d1002      2222222222           1.55
b1002      名称2                  bbb        d1005      5555555555           10.50
b1003      名称3                  ccc        NULL       NULL                 NULL
b1004      名称4                  ddd        NULL       NULL                 NULL
b1005      名称5                  eee        NULL       NULL                 NULL

（所影响的行数为 5 行）

*/

[青锋-SS]

[Quote=引用 5 楼 dobear_0922 的回复:]
婶婶速度真快呀，呵呵
[/Quote]偶少写了东西啦,速度快了就好漏字符.