在 CTE 查詢中重寫 SQL 查詢

我有這張表，我想儲存一串記錄。

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

我想為 MariDB 實現 SQL 查詢，該查詢按 id 列印所有記錄以及所有帶有 reference_id 的記錄。像這樣的東西：

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

我希望當我選擇記錄 66 來獲取所有上下事務時，因為彼此都使用指向它們的 id。我如何使用遞歸 CTE 來實現這一點？有沒有更好的辦法？

unique_id 為 66 的記錄的預期結果：

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

此 SQL 查詢有效：

select t.id, t.unique_id, @uid := t.reference_id reference_id
from (select * from mytable order by id desc) t
join (select @uid := 66) tmp
where t.id = @uid or reference_id=66

https://www.db-fiddle.com/f/33mbBmQRiF5Z8eRoCR3oco/0

但是當我嘗試這個 CTE 查詢時，我無法獲得上述行。

with recursive cte as (
     select t.*
     from mytable t
     where t.id = 66
     union all
     select t.*
     from cte join
          mytable t
          on cte.id = t.reference_id
    )
select *
from cte; 

https://www.db-fiddle.com/f/ppmxX12PJ1rhszp85nJcWp/0

你能幫我看看如何正確地重寫 SQL 查詢嗎？

使用遞歸時必須選擇一個方向CTE。您可以添加另一個遞歸CTE來解釋降序reference_id的

with recursive 
cte as (
     select t.*
     from mytable t
     where t.id = 66
     union all
     select t.*
     from cte join
          mytable t
          on t.id = cte.reference_id
    ),
cte2 as (
     select t.*
     from mytable t
     where t.id = 66
     union all
     select t.*
     from cte join
          mytable t
          on cte.id = t.reference_id
    )
SELECT *
FROM cte
UNION 
SELECT *
FROM cte2
ORDER BY id ASC;

結果

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

DB-小提琴

編輯：

添加了 UNION ALL 的版本

with recursive 
cte as (
     select t.*
     from mytable t
     where t.id = 66
     union all
     select t.*
     from cte join
          mytable t
          on t.id = cte.reference_id
    ),
cte2 as (
     select t.*
     from mytable t
     where t.reference_id = 66
     union all
     select t.*
     from cte2 join
          mytable t
          on cte2.id = t.reference_id
    )
SELECT * 
FROM cte
UNION ALL
SELECT  *
FROM cte2
ORDER BY id ASC;

DB-小提琴

引用自：https://dba.stackexchange.com/questions/228942