sql - SQL 从连接中获取顶级对象

Question

现在正在处理一个查询，我们想了解哪个企业正在为我们引用最下游的订单。我在这里整理了一个非常基本的表格，用于演示目的，列出了 4 家企业。Bar 和 Donut 最终都是由 Foo 推荐的，我希望能够将 Foo 展示为一家企业已经产生了 X 个订单。显然，获取 Foo（来自 Bar）和 Bar（来自 Donut）的单一推荐是简单的连接。但是你如何从 Bar 回到 Foo 呢？

我要补充一点，我在这个 AM 上做了更多的谷歌搜索，发现了一些关于顶级父级的非常相似的问题，并且大多数回复都建议递归 CTE。我已经有一段时间没有深入研究 SQL 的东西了，但是 8 年前我知道这些东西并不太流行。还有其他方法吗？在订购时将父 ID 存储在订单表上也许更好？

+----+--------+--------------------+
| Id |  Name  | ReferralBusinessId |
+----+--------+--------------------+
|  1 | Foo    |                    |
|  2 | Bar    |                  1 |
|  3 | Donut  |                  2 |
|  4 | Coffee |                    |
+----+--------+--------------------+

标签： sqljoinazure-sql-database

Answer 1

WITH RECURSIVE entity_hierarchy AS (
  SELECT id, name, parent FROM entities WHERE name = 'Donut'
UNION
  SELECT e.id, e.name, e.parent FROM entities e INNER JOIN entity_hierarchy eh on e.id = eh.parent
)
SELECT id, name, parent FROM entity_hierarchy;

SQL 小提琴示例