MySQL CTE递归与连接另一个表

本教程将介绍MySQL CTE递归与连接另一个表的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

MySQL CTE递归与连接另一个表 教程 第1张

问题描述

伟大的开发人员您好!

我有一个案例,带有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
chiiraChiira 11101100 => 1000
rdpChloe  11001000
lotus Lotus  11111110 => 1100 => 1000

Demo on dbfiddle

好了关于MySQL CTE递归与连接另一个表的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。