mysql - 如何为项目管理任务记录的顺序跟踪编写递归 MySQL 查询

Question

我正在尝试重构项目管理系统，我想帮助编写递归 MySQL 查询以顺序跟踪项目中的所有任务。我认为它很可能需要一个存储过程。

考虑两个项目:

里程碑 A 到里程碑 F - 10 项任务(参见项目图表说明):

里程碑 U 到里程碑 W - 6 项任务(参见项目图表说明):

将这些项目实现为两个 MySQL 表 - proj 和 proj_task，表 proj 列出了所有 16 个项目任务，而 proj_task 表描述了每个任务的关系，在 id_parent 字段中列出了父任务，在 id_child 字段中列出了子任务:

CREATE TABLE IF NOT EXISTS `proj` (
  `id` int(13) NOT NULL AUTO_INCREMENT,
  `project_task_event` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `proj` (`id`, `project_task_event`) VALUES
(1, 'Task A'),
(2, 'Task B'),
(3, 'Task C'),
(4, 'Task D'),
(5, 'Task E'),
(6, 'Task F'),
(7, 'Task G'),
(8, 'Task H'),
(9, 'Task I'),
(10, 'Task J'),
(11, 'Task U'),
(12, 'Task V'),
(13, 'Task W'),
(14, 'Task X'),
(15, 'Task Y'),
(16, 'Task Z');

CREATE TABLE IF NOT EXISTS `proj_task` (
  `id` int(13) NOT NULL AUTO_INCREMENT,
  `id_parent` int(13) NOT NULL,
  `id_child` int(13) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_parent` (`id_parent`,`id_child`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `proj_task` (`id`, `id_parent`, `id_child`) VALUES
(1, 0, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4),
(5, 3, 5),
(6, 4, 5),
(7, 5, 6),
(8, 1, 7),
(9, 1, 8),
(10, 7, 9),
(11, 8, 9),
(12, 9, 10),
(13, 10, 6),
(14, 0, 11),
(15, 11, 12),
(16, 12, 13),
(17, 11, 14),
(18, 14, 15),
(19, 15, 13),
(20, 11, 16),
(21, 16, 13);

A 和 U 这两个任务没有父级 (id_parent = 0)，因此根据定义，它们各自定义了一个新项目:

select p.`project_task_event` as 'Projects' from `proj` p, `proj_task` t where p.id = t.`id_child` and t.`id_parent` = 0;
+----------+
| Projects |
+----------+
| Task A   |
| Task U   |
+----------+

任何人都可以帮我写一个递归的 MySQL 查询来选择每个项目的任务(下面的示例输出)，只使用上面的表吗？我正在寻找的确切输出是列出项目任务 (proj.project_task_event)，它们是给定项目起始父项的子后代(其中 proj_task.id_parent = 0)。父级可以通过其唯一的 proj.id 或其唯一的 proj_task.id 来引用。

+---------------+
| Project Tasks |
+---------------+
| Task A        |
| Task B        |
| Task C        |
| Task D        |
| Task E        |
| Task F        |
| Task G        |
| Task H        |
| Task I        |
| Task J        |
+---------------+

+---------------+
| Project Tasks |
+---------------+
| Task U        |
| Task V        |
| Task W        |
| Task X        |
| Task Y        |
| Task Z        |
+---------------+

目前，系统在 proj 表中有一个额外的字段，这使得上面的 SELECT 查询变得微不足道，但我想删除这个字段，因为所有必要的数据似乎都存在进行递归查询:

CREATE TABLE IF NOT EXISTS `proj` (
  `id` int(13) NOT NULL AUTO_INCREMENT,
  `project_task_event` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `proj_id` int(13) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `proj_id` (`proj_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

INSERT INTO `proj` (`id`, `project_task_event`, `proj_id`) VALUES
(1, 'Task A', 1),
(2, 'Task B', 1),
(3, 'Task C', 1),
(4, 'Task D', 1),
(5, 'Task E', 1),
(6, 'Task F', 1),
(7, 'Task G', 1),
(8, 'Task H', 1),
(9, 'Task I', 1),
(10, 'Task J', 1),
(11, 'Task U', 11),
(12, 'Task V', 11),
(13, 'Task W', 11),
(14, 'Task X', 11),
(15, 'Task Y', 11),
(16, 'Task Z', 11);

select `project_task_event` as 'Project Tasks' from `proj` where `proj_id` = 1;
+---------------+
| Project Tasks |
+---------------+
| Task A        |
| Task B        |
| Task C        |
| Task D        |
| Task E        |
| Task F        |
| Task G        |
| Task H        |
| Task I        |
| Task J        |
+---------------+

select `project_task_event` as 'Project Tasks' from `proj` where `proj_id` = 11;
+---------------+
| Project Tasks |
+---------------+
| Task U        |
| Task V        |
| Task W        |
| Task X        |
| Task Y        |
| Task Z        |
+---------------+

Answer 1

看看这个:

   select DISTINCT a.id,a.project_task_event,a.proj_id,a.id_parent,a.id_child,b.project_task_event from 
(select a.id,project_task_event,proj_id,id_parent,id_child from 
(select id,project_task_event,proj_id from proj) as a
left JOIN
(select id,id_parent,id_child from proj_task)as b
on a.id = b.id_parent) as a

left JOIN


(select a.id,project_task_event,proj_id,id_parent,id_child from 
(select id,project_task_event,proj_id from proj) as a
left JOIN
(select id,id_parent,id_child from proj_task)as b
on a.id = b.id_parent)as b
on a.id_child = b.id

结果:

这是给出的示例: