联合查询2个表_LAMP中文网

表A有字段id, cusName(varchar), deviceCode(varchar)
表B有字段id, gpsTime(datetime), deviceCode(varchar)
我想查询表A中所有的deviceCode的最新一条记录，最新记录是根据表B中gpsTime来查的。表A与表B deviceCode外键关连

得到的结果为
A.id,cusName,A.deviceCode,gpsTime

要求效率优先，查询速度快。麻烦帮忙解决

作者: vison3036 发布时间: 2011-05-13

select * from b a1
inner join a a2 on a1.deviceCode=a2.deviceCode
where not exists(select 1 from b where a1.deviceCode=deviceCode and a1.gpsTime<gpsTime)

作者: wwwwb 发布时间: 2011-05-13

SQL code

select A.id,cusName,A.deviceCode,gpsTime
from A,(select * from B B1 where not exists (select 1 from B where B1.gpsTime<gpsTime))C
where A.deviceCode=C.deviceCode

作者: rucypli 发布时间: 2011-05-13

or
select * from b a1
inner join a a2 on a1.deviceCode=a2.deviceCode
inner join (select deviceCode,max(gpsTime) as ma from b group by deviceCode) c
on a1.deviceCode=c.deviceCode and a1.gpsTime=c.ma

作者: wwwwb 发布时间: 2011-05-13

请问三楼，在查询分析器里不能执行，提示gpsTime错误。
(select deviceCode,max(gpsTime) as ma from b group by deviceCode)

作者: vison3036 发布时间: 2011-05-13

错误信息，你的代码

select deviceCode,max(gpsTime) as ma from b group by deviceCode
没有问题嘛

作者: wwwwb 发布时间: 2011-05-13

联合查询2个表

热门频道