postgresql 在 FROM 中使用来自 JOIN 语句的别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9007797/
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
Using alias from JOIN statement in FROM
提问by megges
I'm trying to build a PostGIS query that uses an alias of the JOIN statement in the FROM statement, but that is not possible. Is there another way to do it?
我正在尝试构建一个 PostGIS 查询，该查询在 FROM 语句中使用 JOIN 语句的别名，但这是不可能的。还有另一种方法吗？
SELECT DISTINCT
ST_Buffer(
ST_Centroid(geom),
ST_Area(geom))
FROM building AS b, ST_Transform(sg.geometry, 31467) AS geom
LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id WHERE ST_Area(geom) < 100
回答by araqnid
You can introduce an extra level of subquery.
您可以引入额外级别的子查询。
So currently you have:
所以目前你有：
SELECT DISTINCT ST_Buffer( ST_Centroid(geom), ST_Area(geom) )
FROM building AS b,
ST_Transform(sg.geometry, 31467) AS geom
LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id
WHERE ST_Area(geom) < 100
(I changed the indentation to make a bit more sense of it, although I still don't understand it: you don't seem to use the values from b anywhere). You need something like:
（我更改了缩进以使其更有意义，尽管我仍然不明白：您似乎没有在任何地方使用 b 中的值）。你需要这样的东西：
SELECT DISTINCT ST_Buffer( ST_Centroid(geom), ST_Area(geom) )
FROM (
SELECT ST_Transform(sg.geometry, 31467) AS geom
FROM building AS b
LEFT JOIN surface_geometry AS sg ON b.id = sg.cityobject_id
) x
WHERE ST_Area(geom) < 100
回答by Karlson
Why would you not try:
你为什么不试试：
SELECT DISTINCT
ST_Buffer(ST_Centroid(ST_Transform(sg.geometry, 31467),
ST_Area(ST_Transform(sg.geometry, 31467)))
FROM building b LEFT JOIN surface_geometry sg ON (b.id = sg.cityobject_id)
WHERE ST_Area(ST_Transform(sg.geometry, 31467)) < 100;
It is also possible that this might work:
这也可能有效：
SELECT DISTINCT
ST_Buffer(ST_Centroid(geom), ST_Area(geom))
FROM building b LEFT JOIN surface_geometry sg ON (b.id = sg.cityobject_id) INNER JOIN
ST_Transform(surface_geometry.geometry, 31467) geom ON (ST_Area(geom) < 100);
You might want to verify the placement of () as at the moment I have no place to test it.
您可能想验证 () 的位置，因为目前我没有地方测试它。
