如何使用SQL选择一对多关系并合并输出 [英] How to SQL Select a one to many relation and merge the output

问题描述

自从我接近解决方案以来，此功能已得到重大更新

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

I guess the title is not the best but I did not know how to explain it better.

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

I have locations with coordinates in two related tables. Table locations(id, name, description, created_at) and locations_coordinates(location_id, lat, lng, coordinates_order).

我存储的坐标数量未知(多边形)，这就是为什么我使用两个表的原因.

I am storing an unknown amount of coordinates (a polygon), that`s why I use two tables.

现在我正在运行以下查询

Now I am running the following query

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)来完成此查询?

So my question is: is there a way to get the needed output with SQL only? If not, can my query be improved, so that I have it easier doing it with application code (PHP for me) ?

更新:

我使用 MyISAM ，在"locations_coordinates"表中，"locations_id"和"coordinates_order"为 PRIMARY ，"coordinates_order"设置为 AUTO INCREMENT ，因此在插入时始终会启动一系列新的订单号. (我需要这样做，以便以后可以按正确的顺序选择坐标.)

I`m using MyISAM and in the "locations_coordinates" table, "locations_id" and "coordinates_order" are PRIMARY and "coordinates_order" is set to AUTO INCREMENT, so it starts always a new series of order numbers when inserted. (I need this so i can select the coordinates in the right order later).

推荐答案

我以这种方式解决了类似的问题:

I resolves similar problems in this way:

首先，获取我需要的所有位置记录，将它们放入ID为键的哈希映射中.

First, get all the location records I need, Put them in a hash map with the ID as key.

第二，获取所有坐标记录，其中location_id在位置记录id中. 也许:从(?，?，...)中location_id的坐标中选择*

Second, get all the coordinate records where location_id in location records id. Maybe: select * from coordinate where location_id in (?, ?, ...)

第三，迭代坐标记录并将lat和lng列设置到相应的位置记录中.

Third, iterate the coordinate records and set lat and lng column into the corresponding location record.

如果位置记录的大小不太大，则仅需要两个SQL语句.因此，我们可以获得更好的性能.

If the size of location records is not too large, it need only two SQL statements. Thus, we get a better performance.

这篇关于如何使用SQL选择一对多关系并合并输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！