mysql - 如何 SQL 选择一对多关系并合并输出

Question

自从我接近解决方案以来，这已经有了显着的更新

我想这个标题不是最好的，但我不知道如何更好地解释它。

我在两个相关表中有带坐标的位置。表 locations(id, name, description, created_at) 和 locations_coordinates(location_id, lat, lng, coordinates_order)。

我正在存储未知数量的坐标(一个多边形)，这就是我使用两个表的原因。

现在我正在运行以下查询

SELECT l.id, 
   l.name, 
   l.description, 
   l.created_at, 
   GROUP_CONCAT(Concat(c.lat, ":", c.lng) ORDER BY c.coordinate_order ASC 
   SEPARATOR 
   ', ') AS coordinates 
FROM   locations l 
   LEFT JOIN locations_coordinates c 
     ON l.id = c.location_id 
WHERE  l.id = ' . $id . ' 
GROUP  BY l.id, 
      l.name, 
      l.description, 
      l.created_at ASC 

所以我得到以下输出(使用 id = 3):

[
{
  "id":"3",
  "name":"Stadthalle",
  "description":"Die Wiener Stadthalle",
  "created_at":"2012-01-07 14:22:06",
  "coordinates":"48.201187:16.334213, 48.200665:16.331606, 48.202989:16.331091,     48.203075:16.334192"
}
] 

我想得到的是纬度和经度对，比如:

[
{
  "id":"3",
  "name":"Stadthalle",
  "description":"Die Wiener Stadthalle",
  "created_at":"2012-01-07 14:22:06",
  "coordinates":[
     [
        "48.201187,16.334213"
     ],
     [
        "48.200665,16.331606"
     ],
     [
        "48.202989,16.331091"
     ],
     [
        "48.203075,16.334192"
     ]
  ]
}
]

所以我的问题是:有没有办法只使用 SQL 获得所需的输出？如果没有，是否可以改进我的查询，以便我可以更轻松地使用应用程序代码(对我来说是 PHP)来完成查询？

更新:

我正在使用 MyISAM，在“locations_coordinates”表中，“locations_id”和“coordinates_order”是PRIMARY，“coordinates_order”设置为AUTO INCREMENT，所以它在插入时总是开始一系列新的订单号。 (我需要这个，以便稍后可以按正确的顺序选择坐标)。

Answer 1

只要您设置了外键(如果需要的话可以设置多个)，但是请确保为这些键建立索引以提高速度。

然后确保您可以像上面提到的那样进行多次连接，只要位置和坐标具有外键 user.id

我前几天回答过一个类似的问题(很有趣，它与位置/坐标有关)，不幸的是我今晚不能点击任何链接转发你，浏览我的个人资料。