SQL 子查询的自连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/907366/
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
Self-join of a subquery
提问by Joril
I was wondering, is it possible to join the result of a query with itself, using PostgreSQL?
我想知道，是否可以使用 PostgreSQL 将查询结果与自身连接起来？
回答by Andomar
You can do so with WITH:
你可以用 WITH 这样做：
WITH subquery AS(
SELECT * FROM TheTable
)
SELECT *
FROM subquery q1
JOIN subquery q2 on ...
Or by creating a VIEW that contains the query, and joining on that:
或者通过创建一个包含查询的 VIEW 并加入该查询：
SELECT *
FROM TheView v1
JOIN TheView v2 on ...
Or the brute force approach: type the subquery twice:
或者蛮力方法：键入子查询两次：
SELECT *
FROM (
SELECT * FROM TheTable
) sub1
LEFT JOIN (
SELECT * FROM TheTable
) sub2 ON ...
回答by Eoin Campbell
Do you mean, the result of a query on a table, to that same table. If so, then Yes, it's possible... e.g.
你的意思是，对一个表的查询结果，到同一个表。如果是这样，那么是的，这是可能的......例如
--Bit of a contrived example but...
SELECT *
FROM Table
INNER JOIN
(
SELECT
UserID, Max(Login) as LastLogin
FROM
Table
WHERE
UserGroup = 'SomeGroup'
GROUP BY
UserID
) foo
ON Table.UserID = Foo.UserID AND Table.Login = Foo.LastLogin
回答by Quassnoi
Yes, just alias the queries:
是的，只是查询别名：
SELECT *
FROM (
SELECT *
FROM table
) t1
JOIN (
SELECT *
FROM table
) t2
ON t1.column < t2.other_column
