用主表和子表关联要求取子表登记时间中最大一条！

[test_wangzw]

如题。我主表和子表关联 我想查询出子表中登记时间最大一条 如何写！

主表：a (主键bh) 子表：b （子健 a_bh,登记时间：djsj）。

[crazylaa]

[Quote=引用 7 楼 ojuju10 的回复:]
select * from ta a left join tb b on a.bh=b.a_bh
where not exists
(select 1 from tb c where b.a_bh=c.a_bh and b.djsj <c.djsj)
[/Quote]
支持，这个应该最快

[ACMAIN_CHM]

select *
from a inner join b t on a.b=t.a_bh
where not exists (select 1
from b where a_bh=a.bh and djsj>t.djsj)

[fatfoxz]

如果子表里没有相应数据，主表的还检索出来吗？

[test_wangzw]

肯定有数据

[fatfoxz]

select b.a_bh, max(b.djsj)
from a
left join b on a.bh = b.a_bh
group by b.a_bh

[fatfoxz]

如果要检索更多的信息，你可以把这个作为子查询来使用。

[Phoenix_99]

select b.a_bh, max(b.djsj)
from a
left join b on a.bh = b.a_bh
group by b.a_bh

[ojuju10]

select * from ta a left join
(select a_bh,max(djsj) as djsj from tb group by a_bh ) b
on a.bh=b.a_bh

[ojuju10]

select * from ta a left join tb b on a.bh=b.a_bh
where not exists
(select 1 from tb c where b.a_bh=c.a_bh and b.djsj<c.djsj)