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

问题描述：

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

考虑两个项目：

里程碑A通过里程碑的F - 10级的任务（见项目图表的图示）：

里程碑ü通过里程碑W - 6个任务（见项目的图示图表）：

实施这些项目s转换两个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  | 
+---------------+

你需要什么确切的输出？ –

@reds我更新了帖子以声明“我正在寻找的确切输出是列出项目任务（'proj'.'project_task_event'），它们是给定项目父项的子后代（其中'proj_task'.'id_parent '= 0）。可以通过其独特的'proj'.'id'或其唯一的'proj_task'.'id'来引用父代。“谢谢。 –

需要在表格中清楚吗？我们只有两个表吗？proj和proj_task表？ –

答

在此请看：

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

结果：

这是给定样本：

这个结果返回到上面的第一个插图，只是想让你检查它。 –

好@reds你优雅的解决方案非常接近，显示所有项目的所有任务。非常感谢。如前所述，我正在寻找重构它没有'proj'.'proj_id'字段。当我简单地从查询中删除'proj'.'proj_id'字段时，我们仍然得到相同的正确结果，显示所有项目中的所有任务。 –

我给出的示例显示了2个项目，但在生产中，这些表可以保存许多项目的数据。对于任务A，如果给定'proj'.''' = 1，我们将如何约束查询以仅显示来自第一个项目的那些任务？或者，对于任务U给定'proj'.'''' = 11，或者交替地约束查询以仅显示来自第二个项目的那些任务？ –

答

您不能任意复杂与sinle查询一个项目做到这一点。 MySQL不提供递归查询（请参阅Oracle's CONNECT BY syntax了解如何在别处实现）。部分解决方案是使用mysql的oq graph engine，但我发现它有点有限，难以与其他数据集集成在查询中。

我建议最好的解决方案是使用过程/函数填充临时表。在伪代码中......

Create temptable (
     Node integer primary key, 
     Gen integer); 

Set generation=0; 

Insert into temptable (node, gen) values (starting_node, generation); 

Do 
    Insert ignore into temptable (node, gen) 
    Select child_id, generation+1 
    From temptable tt 
    Inner join proj_tasks pj 
     On tt.node=pj.parent_id 
    Where tt.gen=generation; 

    If 0 rows inserted or generation ≥ max_iterations break; 

    Set generation = generation+1; 
Repeat; 

Select * from temptable;

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

相关推荐