select a.*,y.step,y.LOGDATE
from a left join (
select * from b x where step=(select max(step) from b where id=x.id)
) as y
on x.id=y.id
from a left join (
select * from b x where step=(select max(step) from b where id=x.id)
) as y
on x.id=y.id
from a inner join b on a.id=b.id
group by a.id,a.name,b.Logdate
select a.id,a.name,b.step,b.logdate from a,b
where a.id=b.id and b.step=(select max(step) from b c where b.id=c.id)
from a inner join b on a.id=b.id
group by a.id,a.name,b.Logdate
create table a(id int,name varchar(20))
create table b(id int,step int,logdate datetime)
insert into a(id,name)
select 1 ,'john'
union
select 2 ,'ian'
union
select 3 ,'tom'
insert into b(id,step,logdate)
select 1 ,1 ,'2004-01-01'
union
select 1 ,2 ,'2004-01-01'
union
select 1 ,3 ,'2004-01-01'
union
select 2 ,1 ,'2004-01-01'
union
select 2 ,2 ,'2004-01-01'
union
select 3, 1 ,'2004-01-01'
select a.id,a.name,b.logdate from a,b
where a.id=b.id and b.step=(select max(step) from b c where b.id=c.id)drop table a
drop table b结果:
1 john 2004-01-01 00:00:00.000
2 ian 2004-01-01 00:00:00.000
3 tom 2004-01-01 00:00:00.000
select a.id,a.name,b.step,b.stat from a,b
where a.id=b.id and b.step=(select max(step) from b where b.id=a.id)ps:为什么结贴给了分怎么显示不出来,管理的时候却显示给了?不明白,-_-!