select a.id,a.name,a.flag,b.id,b.sub_id,b.price
from table1 as a inner join
(select id,max(sub_id) as sub_id,max(price) as price
from table2 group by id ) as b on a.id=b.id
from table1 as a inner join
(select id,max(sub_id) as sub_id,max(price) as price
from table2 group by id ) as b on a.id=b.id
(select id,max(sub_id) from table2 group by id,sub_id ) a on table2.id=a.id and table2.sub_id=a.sub_id
select table1.*,table2.* from table1 inner join table2 on table1.id=table2.id inner join
(select id,max(sub_id) from table2 group by id) a on table2.id=a.id and table2.sub_id=a.sub_id
a.id,
a.name,
a.flag,
c.id,
c.sub_id,
c.price
from
table1 a
inner join
(select b.* from table2 b where b.sub_id =(select top 1 sub_id from table2 where id = b.id)) c
on
a.id=c.id
谢谢