多对多关系的3个表之间的SQL查询 [英] SQL Query between 3-tables of a many-to-many relationship

问题描述

我有三个表:friends，locations，friend_location

friend_location是一个联接表，用于允许friends和locations之间的多对多关系，因此这些表将看起来像这样:

friend_location is a join table to allow the many-to-many relationship between friends and locations, so the tables will look something like this:

朋友

ID  | Name
1   | Jerry
2   | Nelson
3   | Paul

位置

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_location

Friend_ID  | Location_id
1          |  2
2          |  1
3          |  3
2          |  2

我想做的就是获取每个朋友的最新位置.

What I would like to do is get the latest location for each friend.

结果

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

这是我在查看各种示例后尝试过的方法，但是它返回了许多结果，并且是不正确的:

This is what I have tried after looking at various examples, but it returns to many results and is not correct:

    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

任何建议将不胜感激.

Any suggestions would be greatly appreciated.

推荐答案

您可以执行以下操作:

SELECT  f.id, f.name, last_known_date, l.Lat, L.Lon
from Friends f
join
(
    select  f.id, MAX(l.Date) as last_known_date
    from    Friends f
    JOIN    Friend_Location fl on f.ID = fl.Friend_ID
    JOIN    Location l on l.ID = fl.Location_ID
    GROUP BY f.id
) FLMax
on FLMax.id = f.id
join Friend_Location fl on fl.friend_ID = f.ID
join Location l on fl.location_ID = l.ID AND l.Date = FLMax.Last_Known_Date

基本上，您的问题是您要按location.id分组，因为ID是唯一的，这将为您提供所有位置.

Basically your problem is that you are grouping by location.id which will give you all of the locations because the ID is unique.

这仅在朋友在任何一次只能位于1个位置的情况下有效.

This only works if a friend can only be at 1 location at any 1 time.

这篇关于多对多关系的3个表之间的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！