MySQL CTE递归与连接另一个表

伟大的开发人员您好！

我有一个案例，带有MySQL查询。 这里是一个示例架构in SQL Fiddle

表架构：

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

所以，我的目标是单行获取Account表的所有Account_type父项的数据，父项数不受限制，如下所示

============================================================================================
|   id_account   |    account_name     |    account_type      |     account_parent_all     |
============================================================================================
| chiira         | Chiira              | 1110                 | 1100 => 1000               |
| rdp            | Chloe               | 1100                 | 1000                       |
| lotus          | Lotus               | 1111                 | 1110 => 1100 => 1000       |
============================================================================================

如何做到这一点？ 谢谢：)

---

解决方案

您可以使用此递归CTE查询，该查询查找给定id_account的所有父值，然后使用GROUP_CONCAT将这些值联接在一起以获得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