我有三张桌子:friends
, locations
, friend_location
friend_location
是一个连接表,允许多对多关系friends
and locations
,所以表格看起来像这样:
Friends
ID | Name
1 | Jerry
2 | Nelson
3 | Paul
location
ID | Date | Lat | Lon
1 | 2012-03-01 | 34.3 | 67.3
2 | 2011-04-03 | 45.3 | 49.3
3 | 2012-05-03 | 32.2 | 107.2
朋友位置
Friend_ID | Location_id
1 | 2
2 | 1
3 | 3
2 | 2
我想做的是获取每个朋友的最新位置。
Results
ID | Friend | Last Know Location | last know date
1 | Jerry | 45.3 , 49.3 | 2011-04-03
2 | Nelson | 34.3 , 67.3 | 2012-03-01
3 | Paul | 32.2 , 107.2 | 2012-05-03
这是我在查看各种示例后尝试过的方法,但它返回许多结果并且不正确:
select f.id , f.name , last_known_date
from friends f, (
select distinct fl.friend_id as friend_id, fl.location_id as location_id, m.date as last_known_date
from friend_location fl
inner join (
select location.id as id, max(date) as date
from location
group by location.id
) m
on fl.location_id=m.id
) as y
where f.id=y.friend_id
任何建议将不胜感激。