SQL查询一个多一对多的关系

问题描述：

我有三个表：friends，locations，friend_locationSQL查询一个多一对多的关系

friend_location是一个连接表，让friends和locations之间的许多一对多的关系，所以表将是这个样子：

朋友


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

我想这样做的就是让每个朋友的最新位置。

结果


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

任何建议，将不胜感激。

请列出您正在使用的数据库的类型...这很大程度上影响了答案，因为窗口函数使事情变得简单和简洁（但在所有数据库中都不可用）。 – 2012-07-30 03:29:15

您可能需要考虑将最后一个已知日期转换为单独的表格 – cordialgerm 2012-07-30 04:51:39

使用PostgrisSQL。谢谢。 – Cysneros 2012-07-30 06:01:11

答

你可以做这样的事情：

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是唯一的分组。

这只适用于朋友只能在任何一个时间在1个位置。

通过将日期放入Friend_Location表格中，您可能可以更好地规范表格。使用您当前的设计，朋友多次登录同一地点将创建多个地点记录。 – Greg 2012-07-30 03:05:24

非常感谢大家！他们都很棒。我唯一添加的是一个DISTINCT，以确保每次输入只返回一个结果。 – Cysneros 2012-08-02 22:11:22

答

您的数据布局有点奇怪，因为日期位于位置表中。所以，下面的检索每个朋友的最新日期：

select fl.friend_id, max(l.date) as maxdate 
from friend_location fl 
    location l join 
    on fl.location_id = l.location_id

现在，就让我们一起回来的信息，这个查询：

select f.*, maxdate, l.* 
from (select fl.friend_id, max(l.date) as maxdate 
     from friend_location fl 
     JOIN location l 
      on fl.location_id = l.id 
     group by fl.friend_id 
    ) flmax join 
    friends f 
     on flmax.friend_id = f.id 
    join location l 
     on l.date = flmax.maxdate

这将工作，假设位置不有重复的日期。如果他们这样做，查询会有点复杂。我们可以做出这个假设吗？

答

您可以使用：

SELECT 
    a.*, 
    CONCAT(d.Lat, ' , ', d.Lon) AS last_known_location, 
    d.Date AS last_known_date 
FROM 
    friends a 
JOIN 
(
    SELECT a.Friend_ID, MAX(b.Date) AS maxdate 
    FROM  friend_location a 
    JOIN  location b ON a.Location_id = b.ID 
    GROUP BY a.Friend_ID 
) b ON a.ID = b.Friend_ID 
JOIN 
    friend_location c ON b.Friend_ID = c.Friend_ID 
JOIN 
    location d ON c.Location_id = d.ID AND b.maxdate = d.Date

答

Gregs查询看起来正确的给我，我想出了一个类似（见下文）。然而，当两个朋友以不同的顺序访问相同的位置时，当前的数据库模式不处理情况对我而言，Date列应该位于friend_location表中，而不是位置。但是，如果不是那么查询是：

SELECT F.ID, F.Name AS Friend, L.Lat, L.Lon, L.Date 
FROM 
(
    SELECT MAX(L.date) AS max_date, F.ID 
    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 
) AS X 
JOIN Friends F ON X.ID=F.ID 
JOIN friend_location FL ON F.ID=FL.Friend_ID 
JOIN location L ON L.ID=FL.Location_id AND L.Date=X.max_date

SQL查询一个多一对多的关系

相关推荐