select * from tableA as t left join ( select tableB.* from tableB as t1 inner join (select pid,max(create_at) as max_create_at from tableB group by pid) as t2 on t1.pid = t2.pid and t1.create_at = t2.max_create_at ) as t3 on t.pid = t3.pid where t.pid in (1,2,3);
大体是这样,这是Mysql的

 select t2.* from 
(select pid from tableA group by pid) as t1 OUTER APPLY
(select top(1) * from tableA where pid = t1.pid order by created desc) as t2