本文介紹了MySQL CTE遞歸與連接另一個(gè)表的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
偉大的開發(fā)人員您好！
我有一個(gè)案例，帶有MySQL查詢。
這里是一個(gè)示例架構(gòu)in SQL Fiddle
表架構(gòu)：
CREATE TABLE account (
id_account varchar(50),
account_name varchar(50),
account_type varchar(4)
);
INSERT INTO account VALUES
('chiira', 'Chiira', '1110'),
('rdp', 'RDP', '1100');
CREATE TABLE account_type_master (
account_type varchar(4),
account_type_name varchar(50),
account_type_parent varchar(4)
);
INSERT INTO account_type_master VALUES
('1000', 'Master Account', null),
('1100', '2nd Master', '1000'),
('1110', '3rd Master', '1100');
所以，我有2張桌子。
account table
===============================================================
| id_account | account_name | account_type |
===============================================================
| chiira | Chiira | 1110 |
| rdp | Chloe | 1100 |
| lotus | Lotus | 1111 |
===============================================================
account_type_master
===================================================================
| account_type | account_type_name | account_type_parent |
===================================================================
| 1000 | Master Account | null |
| 1100 | 2nd Master | 1000 |
| 1110 | 3rd Master | 1100 |
| 1111 | Last Master | 1110 |
===================================================================
所以，我的目標(biāo)是單行獲取Account表的所有Account_type父項(xiàng)的數(shù)據(jù)，父項(xiàng)數(shù)不受限制，如下所示
============================================================================================
| id_account | account_name | account_type | account_parent_all |
============================================================================================
| chiira | Chiira | 1110 | 1100 => 1000 |
| rdp | Chloe | 1100 | 1000 |
| lotus | Lotus | 1111 | 1110 => 1100 => 1000 |
============================================================================================
如何做到這一點(diǎn)？
謝謝：)
推薦答案
您可以使用此遞歸CTE查詢，該查詢查找給定id_account的所有父值，然后使用GROUP_CONCAT將這些值聯(lián)接在一起以獲得account_parent_all值：
WITH RECURSIVE CTE AS (
SELECT a.id_account, a.account_name, a.account_type, m.account_type_parent, 0 AS depth
FROM account a
JOIN account_type_master m ON m.account_type = a.account_type
UNION ALL
SELECT c.id_account, c.account_name, c.account_type, m.account_type_parent, c.depth + 1
FROM CTE c
JOIN account_type_master m ON m.account_type = c.account_type_parent
WHERE c.account_type_parent IS NOT NULL
)
SELECT c.id_account, c.account_name, c.account_type,
GROUP_CONCAT(c.account_type_parent ORDER BY c.depth SEPARATOR ' => ') AS account_parent_all
FROM CTE c
GROUP BY c.id_account, c.account_name, c.account_type
ORDER BY c.account_name
輸出：
id_account account_name account_type account_parent_all
chiira Chiira 1110 1100 => 1000
rdp Chloe 1100 1000
lotus Lotus 1111 1110 => 1100 => 1000
Demo on dbfiddle
這篇關(guān)于MySQL CTE遞歸與連接另一個(gè)表的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
