为 MariaDB 的分层查询实现递归 CTE [英] Implement Recursive CTE for Hierarchical Query to MariaDB

问题描述

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

CREATE TABLE 表名 (标识 INT，unique_id varchar，reference_id varchar，);

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

<代码>|编号 |唯一标识 |参考ID ||||----|------------|--------------|---|---||43 |55544 |||||45 |45454 |43 ||||66 |55655 |45 ||||78 |88877 |66 ||||99 |第454章33 |||

我希望当我选择记录 55544 来获取所有交易时，因为彼此都使用指向它们的 id.我如何使用递归 CTE 来实现这一点?有没有更好的办法?

unique_id 55544 记录的预期结果:

<代码>|编号 |唯一标识 |参考ID ||||----|------------|--------------|---|---||43 |55544 |||||45 |45454 |43 ||||66 |55655 |45 ||||78 |88877 |66 |||

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

解决方案

以下查询应该符合您的预期.这是一个递归查询，它依赖于一个变量来通过依赖树来跟踪父子关系.

选择@ref:=id 作为id、unique_id、reference_id来自我的表join (select @ref:=id from mytable WHERE unique_id = 55544)tmp其中reference_id=@ref

此DB Fiddle 演示 产生:

<上一页>|编号 |唯一标识 |参考ID ||--- |--------- |------------ ||45 |45454 |43 ||66 |55655 |45 ||78 |88877 |66 |

<小时>

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

其中 reference_id=@ref 或 unique_id = 55544

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,
);

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           |   |   |

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?

Expected result for record with unique_id 55544:

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

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

This demo on DB Fiddle yields :

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

---

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

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