PostgreSQL 错误：子查询必须只返回一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16455045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL error: subquery must return only one column
提问by Nyxynyx
Using PostgreSQL-9.1 and PostGIS 2.0.1, when doing a SELECT query containing a sub-query that returns multiple columns, I am getting the error subquery must return only one column.
使用 PostgreSQL-9.1 和 PostGIS 2.0.1，在执行包含返回多列的子查询的 SELECT 查询时，出现错误subquery must return only one column。
How can the query/subquery be modified to return multiple columns?
如何修改查询/子查询以返回多列？
Query
询问
SELECT l.id, l.lat, l.lng, l.geom,
(SELECT g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM stage.dogs as g
LIMIT 1)
FROM stage.users As l
Full Query
完整查询
SELECT l.id, l.lat, l.lng, l.geom,
g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM stage.users As l
CROSS JOIN (SELECT *
FROM stage.dogs as g
ORDER BY g.geom <-> l.geom
LIMIT 1) as g
Error
错误
ERROR: invalid reference to FROM-clause entry for table "l"
SQL state: 42P01
Hint: There is an entry for table "l", but it cannot be referenced from this part of the query.
采纳答案by RichardTheKiwi
SELECT l.id, l.lat, l.lng, l.geom,
g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM stage.users As l
CROSS JOIN (SELECT * FROM stage.dogs LIMIT 1) as g
This is literally what you had (assuming stage.dogs) is not empty. Not sure if there should be a correlation between usersand dogsthough.
这实际上是您拥有的（假设 stage.dogs）不为空。不确定users和dogs虽然之间是否应该存在相关性。
To find the closest dog to a user, you can use this query. The scalar subquery finds the dog's ID, and is joined back to the table to retrieve the other columns.
要找到离用户最近的狗，您可以使用此查询。标量子查询找到狗的 ID，并连接回表以检索其他列。
SELECT l.id, l.lat, l.lng, l.geom,
g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM (
SELECT l1.*, (SELECT g1.id
FROM stage.dogs as g
ORDER BY g.geom <-> l.geom
LIMIT 1) g_id
FROM stage.users As l1
) l
JOIN stage.dogs as g ON g.id = l.g_id;
Fair warning that this will NOT be a fast query.
公平警告，这将不是一个快速查询。
At the risk of performing even slower, see query below for multiple tables
冒着执行更慢的风险，请参阅下面的查询以获取多个表
SELECT l.id, l.lat, l.lng, l.geom,
g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom) dog_distance,
c.id, c.lat, c.lng, ST_Distance(l.geom, c.geom) cat_distance,
b.id, b.lat, b.lng, ST_Distance(l.geom, b.geom) bird_distance
FROM (
SELECT l1.*, (SELECT g1.id
FROM stage.dogs as g1
ORDER BY g1.geom <-> l.geom
LIMIT 1) dog_id,
(SELECT c1.id
FROM stage.cats as c1
ORDER BY c1.geom <-> l.geom
LIMIT 1) cat_id,
(SELECT b1.id
FROM stage.cats as b1
ORDER BY b1.geom <-> l.geom
LIMIT 1) bird_id
FROM stage.users As l1
) l
LEFT JOIN stage.dogs as g ON g.id = l.dog_id
LEFT JOIN stage.dogs as c ON c.id = l.cat_id
LEFT JOIN stage.dogs as b ON b.id = l.bird_id;
回答by Erwin Brandstetter
This gives you one row per user with the closest dog:
这为每个用户提供了与最近的狗的一行：
SELECT DISTINCT ON (l.id)
l.id, l.lat, l.lng, l.geom
,g.id, g.lat, g.lng, ST_Distance(l.geom, g.geom)
FROM stage.users l
CROSS JOIN stage.dogs g
ORDER BY l.id, (l.geom <-> g.geom)
More information on the technique with DISTINCT ONin this related answer:
DISTINCT ON此相关答案中有关该技术的更多信息：
I guess if you have GiST indexon g.geom, the planner might be smart enough to just pick the closes item from it. Not sure, didn't test. Otherwise, this kind of CROSS JOINwould result in O(N2) and performance may get out of hand quickly with a bigger table.
我想如果你有GiST的索引上g.geom，规划者可能是足够聪明，只是从中挑选关闭项目。不确定，没有测试。否则，这种情况CROSS JOIN会导致 O(N2) 并且性能可能会在更大的表中迅速失控。
I quote the Postgis manual here:
我在这里引用 Postgis 手册：
Index only kicks in if one of the geometries is a constant (not in a subquery/cte). e.g.
'SRID=3005;POINT(1011102 450541)'::geometryinstead ofa.geom
索引仅在其中一个几何图形是常量（不在子查询/cte 中）时才起作用。例如
'SRID=3005;POINT(1011102 450541)'::geometry代替a.geom
So you may be out of luck here.
所以你在这里可能不走运。
According to the manual you may need to order by ST_Distance()to get precise sort order, but you shouldn't be getting the one furthest away. That makes no sense.
根据手册，您可能需要按顺序ST_Distance()排序以获得精确的排序顺序，但您不应该得到最远的一个。这是没有意义的。
