在另一个查询中使用表别名遍历树 [英] Use table alias in another query to traverse a tree

问题描述

我有以下查询:

select * from (
   select p1.c as child, p1.p as parent, 1 as height
   from parent as p1
   where (p1.c=3 or p1.c=8);

   union

   select p2.c as child, p2.c as parent, 2 as height
   from parent as p2 
   where (p1.child=3 or p1.child=8) and p1.parent = p2.child;
)

架构为:

CREATE TABLE parent(p int, c int);

我正在尝试找到从 child 到 root 的路径. 并添加我们必须遍历的边的数量.
目标是将孩子的父母与父母一起加入，像这样:

I'm trying to find a path from the child to the root. And append the number of edges we have to traverse.
The goal is to join the child's parent with its parent, something like:

(8, 2, 1)
(8, 5, 2) -> 8 is the lowest child, 2 is its parent, and 5 it's 2 parent.

一些示例数据:

10 | 5
10 | 12
12 | 3
12 | 4
4  | 6
4  | 7
5  | 11
5  | 2
2  | 8

如何在构成p2的第二个查询中使用第一个查询p1的引用? 在那之后我应该有;

How can I use the reference for the first query p1 inside the second query that will form p2? After that I should have;

(8,2,1)
(3,12,1)
(3,10,2)
(8,5,2)

因此，我已经知道该怎么做才能完成我想要的事情.

Thus I already will know what to do to complete what I want.

推荐答案

问的问题

您不能引用同一级别(或UNION查询的另一段)中另一个查询中的一个子查询中的表别名.表别名仅在查询本身及其子查询中可见.
您可以使用 LATERAL JOIN .示例:
使用分组

Question asked

You cannot reference a table alias from one subquery in another query on the same level (or in another leg of a UNION query). A table alias is only visible in the query itself and subqueries of it.
You could reference output columns of a subquery on the same query level with a LATERAL JOIN. Example:
Find most common elements in array with a group by

对于少数几个级别(如果您知道个最大级别)，则可以使用一个简单的查询:

For only a handful of levels (if you know the maximum), you can use a simple query:

• LEFT JOIN到表本身的n-1个实例
• 使用COALESCE和CASE语句来确定根和高度，

• LEFT JOIN to n-1 instances of the table itself
• Use COALESCE and a CASE statement to pin down the root and hight,

SELECT p1.c AS child, COALESCE(p3.p, p2.p, p1.p) AS parent
      ,CASE
          WHEN p3.p IS NOT NULL THEN 3
          WHEN p2.p IS NOT NULL THEN 2
          ELSE 1
       END AS height
FROM   parent p1
LEFT   JOIN parent p2 ON p2.c = p1.p
LEFT   JOIN parent p3 ON p3.c = p2.p
WHERE  p1.c IN (3, 8)
ORDER  BY p1.c;

这是标准的SQL，应该在您标记的所有4个RDBMS中都有效.

This is standard SQL and should work in all 4 RDBMS you tagged.

使用像@Ken这样的递归CTE

Use a recursive CTE like @Ken already advised.

• 在递归腿中，每行保持孩子，只让父母前进.
• 在外部SELECT中，仅保留每个孩子的height最大的行.

• In the recursive leg keep the child for every row, only advance the parent.
• In the outer SELECT, only keep a the row with the greatest height per child.

WITH RECURSIVE cte AS (
   SELECT c AS child, p AS parent, 1 AS height
   FROM   parent
   WHERE  c IN (3, 8)

   UNION ALL

   SELECT c.child, p.p AS parent, c.height + 1
   FROM   cte    c
   JOIN   parent p ON p.c = c.parent
   -- WHERE  c.height < 10  -- to safeguard against endless loops if necessary
   )
SELECT DISTINCT ON (child) *
FROM   cte
ORDER  BY child, height DESC;

DISTINCT ON特定于 Postgres .说明:
选择每个GROUP BY组中的第一行?

DISTINCT ON is specific to Postgres. Explanation:
Select first row in each GROUP BY group?

其余的将在 Oracle 甚至是 SQLite 中以类似的方式工作，但在不支持CTE的MySQL中则不会.

The rest would work in a similar fashion in Oracle and even SQLite, but not in MySQL which doesn't support CTEs.

SQL小提琴 展示了这两者.

SQL Fiddle demonstrating both.

这篇关于在另一个查询中使用表别名遍历树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！