sql - 子查询的结果可以与自身连接吗？

Question

假设我需要找到每个动物园中最老的动物。这是典型的最大组查询类型。只有这里有一个复杂的问题:斑马和长颈鹿存储在不同的表中。要获得所有动物的列表，无论是长颈鹿还是斑马，我可以这样做:

(SELECT id,zoo,age FROM zebras
 UNION ALL
 SELECT id,zoo,age FROM giraffes) t1

然后给定 t1，我可以构建一个典型的最大组查询:

SELECT t1.*
FROM t1
JOIN
(SELECT zoo,max(age) as max_age
 FROM t1
 GROUP BY zoo) t2
ON (t1.zoo = t2.zoo)

很明显，我可以将 t1 存储为一个临时表，但是有没有一种方法可以在一个查询中完成这一切，而不必重复 t1 的定义？ (请不要讨论对表设计的修改；我想专注于处理子查询结果的问题。)

Answer 1

这里是 with 子句的链接。

Understanding the WITH Clause

with t1 as
(select id, zoo, age from zebras
union all
select id, zoo, age from giraffes)
select t1.*
from t1
join
(SELECT zoo,max(age) as max_age
FROM t1
GROUP BY zoo) t2
on (t1.zoo = t2.zoo);

注意:您也可以将 t2 移到 with 子句中。

注意 2:另一种解决方案是简单地将 t1 创建为 View 并在您的查询中使用它。