在连接期间在自己的嵌套select语句中使用表from select result

Question

这可能是对问题的重复或不准确的描述，如果是这样的话，请原谅。是否可以使用子从连接中的嵌套select语句中的以下查询中获取，如下所示:

SELECT 
*
FROM
(
SELECT
several_values_from_several_tables
FROM
table1 t1
JOIN multiple_tables ON t1_and_eachother
WHERE
several_conditions_including_nested_select
ORDER BY
multiple_columns
) sub
INNER JOIN (
SELECT
some_id, MAX(some_time_value) AS max_time
FROM
sub
GROUP BY
some_id
) sub2 ON sub.some_id = sub2.some_id
WHERE
sub.time = sub2.time

我想在join选择语句中使用sub，以避免重复相同的选择语句，因为它非常大且昂贵(我还在不同的时间戳上有第二个连接，这将导致执行相同的昂贵查询3次)。我猜直到执行连接和where子句才创建子句。如果有人找到了一个解决方案或变通方法，以实现相同的结果在一个单一的查询之前，我将非常感谢一些指针。

Answer 1

CTE (Common Table Expression，即WITH保理子句)可用于此，例如

WITH
sub
AS
(  SELECT several_values_from_several_tables
FROM table1 t1 JOIN multiple_tables ON t1_and_eachother
WHERE several_conditions_including_nested_select
ORDER BY multiple_columns),
sub2
AS
(  SELECT some_id, MAX (some_time_value) AS max_time
FROM sub
GROUP BY some_id)
SELECT *
FROM sub s JOIN sub2 s2 ON s.some_id = s2.some_id
WHERE s.time = s2.time

Answer 2

在这种情况下，您应该避免自连接并使用分析函数:

SELECT *
FROM   (
SELECT several_values_from_several_tables,
MAX(some_time_value) OVER (PARTITION BY some_id) AS max_time
FROM   table1 t1
JOIN multiple_tables ON t1_and_eachother
WHERE  several_conditions_including_nested_select
ORDER BY
multiple_columns
)
WHERE  some_time_value = max_time

注意:您也可以使用RANK或DENSE_RANK解析函数来代替MAX。