关于连表查询的问题.谢谢!

[lhw68500]

我有两个表,A表(id为主键),B表(IDB为自增长主键,有列ID与A表关联)
现在A中是一条记录,B 中可以对于多条记录,我想取到的结果是A的所有信息和连到B表的最后一条记录信息(也就是B表的最新记录).

[dawugui]

假设B有个时间字段.

select a.* , m.* from A,
(
  select t.* from b where 时间 = (select max(时间) from B where id = t.id)) m
where a.id = b.id

[pt1314917]

select a.*,b.* from a , b 
where a.id=b.id
and not exists(select 1 from b t where id=t.id and idB>t.idB)

--or
select a.*,b.* from a , b 
where a.id=b.id
and (select count(distinct idb) from b t where id=t.id and idB>=t.idB)=1

[$扫地僧$]

select A.*,B.*
 from A ,
     (
        select B1.* from B B1,
                      (select ID,Max(IDB) as IDB from B group by ID) B2
                 where B1.ID=B2.ID and B2.IDB=B1.IDB
     )B
where A.ID=B.ID

[areswang]

select * from ta a
left join(
select * from tb b where not exists(select 1 from tb where id=b.id and idb>b.idb )
) b
on a.id =b.id

[wlinglong]

Select * From a,b Where a.id = b.id and b.idb in (Select max(idb) From b Group By id)

[dawugui]

假设B有个时间字段.

select a.* , m.* from A,
(
select t.* from b where 时间 = (select max(时间) from B where id = t.id)) m
where a.id = m.id

[lhw68500]

先谢谢各位!
我要是这样的结果
A表
id username
1 刘**
2 夏**


B表
IDB A.id address
1 1 四川
2 1 北京
3 2 河北
4 2 河南

我要连表去出结果是
a.id b,idb a.username address
1 2 刘** 北京
2 4 夏** 河南

[pt1314917]

select a.id,b.idb,a.username,b.addrss from a , b 
where a.id=b.id
and not exists(select 1 from b t where id=t.id and idB>t.idB)

--or
select a.id,b.idb,a.username,b.addrss  from a , b 
where a.id=b.id
and (select count(distinct idb) from b t where id=t.id and idB>=t.idB)=1

[lhw68500]

谢谢各位,其实我已经自己写拉个SQL把结果显示出来拉,只是效率比较低,看看各位有没有更好的办法,再次感谢各位的帮助!