求一sql语句(简单问题)
要求显示数据:求物品一览信息,A:基本信息(pid,name,dept...),B:借出信息(存借还的详细信息)(pid,pno,time1,time2,memo...)
说明表间的关系:B.pno=A.pid,
对于A中的一条记录,在B中可能存在多条(也可能不存在).
现要求用一句SQL语句显示,所有物品信息(a.name,a.dept,b.time2,b.memo...),若对于A的记录,在B中存在多条,则显示最近(order by time2 desc)那条的time2,memo.
谢谢.

[解决办法]

select * from a,
(select max(time2),pno from b
group by pno) b
where a.pid=pno
[解决办法]
select a.name,a.dept,b.time2,b.memo
from a join b on a.pid = b.pid
and b.time2 = ( select max(time2) from b where b.pid = a.pid )
[解决办法]
select * from a left join
(select max(time2),pno from b
group by pno) b
on a.pid=pno

[解决办法]
select top 1 a.* froma left join b on a.pid=b.pno order by b.time2 desc
[解决办法]
select * from A
left join B
on B.pno = A.pid and B.pid =(select max(B.pid) from B where B.pno = A.pid)
order by
A.pid asc
[解决办法]
select a.pid,a.dept,e.time2,e.memo
from a,
(
select b.pid,b.time2,b.memo
from b as c,
(select pid,max(time2)
from b
where pid
group by b.pid) as d
where b.pid=c.pid and b.time2=c.time2
) as e
where a.pid=e.pid
[解决办法]
有点匆忙，上面打错了，改为（有事急着出去了）：select a.pid,a.dept,e.time2,e.memo
from a,
(
select c.pid,c.time2,c.memo
from b as c,
(select pid,max(time2)
from b
where pid
group by b.pid) as d
where c.pid=d.pid and c.time2=d.time2
) as e
where a.pid=e.pid