实现递归CTE以对MariaDB进行分层查询 [英] Implement Recursive CTE for Hierarchical Query to MariaDB

问题描述

我有这张表，我想存储一条记录链.

I have this table which I would like to store a chain of records.

CREATE TABLE table_name (
    id INT,
    unique_id varchar,
    reference_id varchar,
);

我想为MariDB实现SQL查询，该查询通过unique_id和所有记录reference_id打印所有记录.像这样:

I want to implement SQL query for MariDB which prints all records by unique_id with all record reference_id. Something like this:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |
| 99 | 454       | 33           |   |   |

我希望选择记录55544来获取所有交易，因为彼此之间都使用指向它们的ID.我如何使用递归CTE来实现呢?有更好的方法吗?

I would like when I select record 55544 to get all transactions because each other are using id which points to them. How I can implement this using Recursive CTE? Is there a better way?

具有唯一ID 55544的记录的预期结果:

Expected result for record with unique_id 55544:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |

如何在HQL中也可以实现此查询?我想在JPA中使用它吗?

How this query can be implemented also in HQL? I want to use it in JPA?

推荐答案

以下查询应能满足您的期望.这是一个递归查询，它依赖于变量来通过依赖关系树遵循父子关系.

The following query should do what you expect. It's a recursive query that relies on a variable to follow the parent-child relationships trough the dependency tree.

select @ref:=id as id, unique_id, reference_id
from mytable
join (select @ref:=id from mytable WHERE unique_id = 55544)tmp
where reference_id=@ref

此 DB Fiddle上的演示 :

This demo on DB Fiddle yields :

| id  | unique_id | reference_id |
| --- | --------- | ------------ |
| 45  | 45454     | 43           |
| 66  | 55655     | 45           |
| 78  | 88877     | 66           |

---

PS:请注意，这不会返回最上面的父行.如果还需要，可以将WHERE条件更改为:

---

PS : please note that this does not return the uppermost parent row. If you need it as well, you can change the WHERE condition to :

where reference_id=@ref or unique_id = 55544

这篇关于实现递归CTE以对MariaDB进行分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！