检索递归父级嵌套列表的查询，该表连接到另外两个表MySQL-PHP

Question

我有3张桌子，我想根据以下条件把它们连接起来。我的第一个表名为components，它有主父表，它有一个名为sub_compnents的子组件，它从父表中保存component_id。第三个表名为activities，该表与自身有一个父-子关系，并具有一个sub_component_id。期望parent_activity_id保存父活动id，is_activity是一个布尔值，我用来存储额外的值，例如1.1.1、A，如果它的值是false，则由parent_activity_id来显示或分组相关的活动，而is_activity是true，那么在循环时间上将使用罗马数字I.II、iii等。

我的表格设计如下

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
-- --------------------------------------------------------

--
-- Table structure for table `tbl_component`
--

CREATE TABLE `tbl_component` (
  `component_id` int NOT NULL,
  `component_name` varchar(200) NOT NULL,
  `component_description` text NOT NULL,
  `created_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `tbl_component`
--

INSERT INTO `tbl_component` (`component_id`, `component_name`, `component_description`, `created_time`) VALUES
(1, 'Integrated Rangeland Development and Management (IRDM)', 'Integrated Rangeland Development and Management (IRDM) Integrated Rangeland Development and Management (IRDM)', '2021-10-05 10:29:18'),
(2, 'Livelihood Improvement & Diversification (LID) ', 'Livelihood Improvement & Diversification (LID) ', '2021-10-05 10:51:17'),
(3, 'Improving Basic Services & Capacity Building (IBSCB)', 'Improving Basic Services & Capacity Building (IBSCB)', '2021-10-05 10:51:17'),
(4, 'Project Management, Monitoring and Evaluation', 'Project Management, Monitoring and Evaluation', '2021-10-05 10:51:17');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_llrp_activity`
--

CREATE TABLE `tbl_llrp_activity` (
  `llrp_activity_id` int NOT NULL,
  `llrp_activity_name` varchar(200) NOT NULL,
  `llrp_activity_description` text,
  `unit_id` int NOT NULL,
  `budget` varchar(13) NOT NULL,
  `male_beneficiary` int NOT NULL DEFAULT '0',
  `female_beneficiary` int NOT NULL DEFAULT '0',
  `youth_beneficiary` int NOT NULL DEFAULT '0',
  `llrp_activity_created_time` timestamp NULL DEFAULT NULL,
  `llrp_activity_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `component_id` int NOT NULL,
  `sub_component_id` int NOT NULL,
  `is_activity` tinyint(1) NOT NULL,
  `parent_llrp_activity_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `tbl_llrp_activity`
--

INSERT INTO `tbl_llrp_activity` (`llrp_activity_id`, `llrp_activity_name`, `llrp_activity_description`, `unit_id`, `budget`, `male_beneficiary`, `female_beneficiary`, `youth_beneficiary`, `llrp_activity_created_time`, `component_id`, `sub_component_id`, `is_activity`, `parent_llrp_activity_id`) VALUES
(1, 'Consultative Capacity Building and Formulation of Project Documents', 'Consultative Capacity Building and Formulation of Project Documents', 0, '', 0, 0, 0, '2021-11-17 12:36:36', 1, 1, 0, 0),
(2, 'Training of Practitioners/Staffs, Community Consultations, Formulation, Validation, Publication and Familiarization of RMIPs', 'Training of Practitioners/Staffs, Community Consultations, Formulation, Validation, Publication and Familiarization of RMIPs', 0, '', 0, 0, 0, '2021-11-19 07:55:39', 1, 1, 0, 1),
(3, 'Training on  Physical and Biological Soil and Water Conservation Activities', 'Training on  Physical and Biological Soil and Water Conservation Activities', 0, '', 0, 0, 0, '2021-11-19 07:57:03', 1, 1, 0, 1),
(4, 'Refresher Trainings for TF Members and Local Facilitators ', 'Refresher Trainings for TF Members and Local Facilitators ', 3, '20000000', 200, 150, 100, '2021-11-19 09:38:11', 1, 1, 1, 2),
(5, 'Community Consultations, Training, Planning (Formulation of RMIPs) & Familiarization Activities', 'Community Consultations, Training, Planning (Formulation of RMIPs) & Familiarization Activities', 1, '', 0, 0, 0, '2022-01-21 11:09:34', 1, 1, 0, 1),
(6, 'Refresher Trainings for TF Members and Local Facilitators ', 'Refresher Trainings for TF Members and Local Facilitators ', 4, '20000000', 200, 150, 100, '2022-01-21 11:10:37', 1, 1, 0, 1),
(7, 'Formulation and Consolidation of RMIP', 'Formulation and Consolidation of RMIP', 1, '', 0, 0, 0, '2022-01-21 11:11:33', 1, 1, 0, 1),
(8, 'Formulation and Consolidation of RMIP', 'Formulation and Consolidation of RMIP', 3, '2000000', 200, 200, 100, '2022-01-21 11:12:49', 1, 1, 0, 1),
(9, 'Validation Workshop of RMIP ', 'Validation Workshop of RMIP ', 1, '3000000', 100, 2000, 100, '2022-01-21 11:13:28', 1, 1, 0, 1),
(10, 'Development of National Level RMIP Summary Document', 'Development of National Level RMIP Summary Document', 3, '10000', 100, 100, 20, '2022-01-21 11:14:18', 1, 1, 0, 1),
(11, 'Publication of RMIP Documents (Copies)', 'Publication of RMIP Documents (Copies)', 4, '2000', 100, 100, 100, '2022-01-21 11:19:14', 1, 1, 0, 1),
(12, 'Familiarization Workshop of RMIPs for Regional & Federal Stakeholders', 'Familiarization Workshop of RMIPs for Regional & Federal Stakeholders', 3, '50000', 90, 90, 50, '2022-01-21 11:20:20', 1, 1, 0, 1),
(13, 'Training on SWC Practices at Cluster Level', 'Training on SWC Practices at Cluster Level', 3, '6000', 100, 100, 20, '2022-01-21 11:22:13', 1, 1, 0, 3),
(14, 'Training on Water Spreading Weir and Gully Control Practices  at Regional Level', 'Training on Water Spreading Weir and Gully Control Practices  at Regional Level', 3, '30000', 100, 100, 10, '2022-01-21 11:22:59', 1, 1, 0, 3),
(15, 'Training of DAs on SWC Practices at Woreda Level', 'Training of DAs on SWC Practices at Woreda Level', 3, '25000', 250, 300, 150, '2022-01-21 11:26:08', 1, 1, 0, 3),
(16, 'Training on  Rangeland Management, Forage Development and Nursery Site Establishment Practices', 'Training on  Rangeland Management, Forage Development and Nursery Site Establishment Practices', 1, '', 0, 0, 0, '2022-01-21 11:33:57', 1, 1, 0, 1),
(17, 'Training on Forage Development & Management at Cluster Level ', 'Training on Forage Development & Management at Cluster Level ', 3, '233000', 200, 250, 150, '2022-01-21 11:37:06', 1, 1, 0, 16),
(18, 'Training of DAs on Forage Development & Management  at Woreda Level', 'Training of DAs on Forage Development & Management  at Woreda Level', 3, '100000', 100, 60, 50, '2022-01-21 11:38:21', 1, 1, 0, 16),
(19, 'Trainings on GPS, GIS and Remote Sensing Applications ', 'Trainings on GPS, GIS and Remote Sensing Applications ', 1, '', 0, 0, 0, '2022-01-21 11:39:41', 1, 1, 0, 1),
(20, 'Training on GPS, GIS and Remote Sensing Applications at Federal Level', 'Training on GPS, GIS and Remote Sensing Applications at Federal Level', 3, '60000', 10, 30, 5, '2022-01-21 11:40:27', 1, 1, 0, 19),
(21, 'Training on GPS, GIS and Remote Sensing Applications at Regional Level', 'Training on GPS, GIS and Remote Sensing Applications at Regional Level', 3, '600000', 130, 120, 60, '2022-01-21 11:41:01', 1, 1, 0, 19),
(22, 'Training on GPS, GIS and Remote Sensing Applications at Cluster Level', 'Training on GPS, GIS and Remote Sensing Applications at Cluster Level', 3, '60000', 200, 120, 30, '2022-01-21 11:41:42', 1, 1, 0, 19),
(23, 'Procurement of Handheld GPS Receivers ', 'Procurement of Handheld GPS Receivers ', 4, '560000', 120, 2000, 2000, '2022-01-21 11:42:49', 1, 1, 0, 19),
(24, 'Trainings to Capacitate Regional  & Cluster Level Project Staffs & Implementing Partners', 'Trainings to Capacitate Regional  & Cluster Level Project Staffs & Implementing Partners', 1, '', 0, 0, 0, '2022-01-21 11:43:42', 1, 1, 0, 1),
(25, 'Training on Strategic Investment Guidelines at Regional Level (7 Per Region & 3 Per Cluster)', 'Training on Strategic Investment Guidelines at Regional Level (7 Per Region & 3 Per Cluster)', 3, '7500000', 60, 50, 10, '2022-01-21 11:44:38', 1, 1, 0, 24),
(26, 'Training on Contract Administration and Construction Supervision  at Regional Level ', 'Training on Contract Administration and Construction Supervision  at Regional Level ', 3, '6000', 30, 50, 25, '2022-01-21 11:45:25', 1, 1, 0, 24),
(27, 'Training on Planning, Study and Design of Low-Traffic Volume Roads and Crossing Structures at Federal Level ', 'Training on Planning, Study and Design of Low-Traffic Volume Roads and Crossing Structures at Federal Level ', 3, '25000', 120, 60, 30, '2022-01-21 11:46:17', 1, 1, 0, 24),
(28, 'Training on Planning, Study and Design of Headwork, Water Supply and Irrigation Structures (Surface and Groundwater Sources) [Carried-over activity from 2013] at Federal Level ', 'Training on Planning, Study and Design of Headwork, Water Supply and Irrigation Structures (Surface and Groundwater Sources) [Carried-over activity from 2013] at Federal Level ', 3, '25000', 30, 20, 12, '2022-01-21 11:47:21', 1, 1, 0, 24),
(29, 'Training on 3ds Max for 3D Design, Modeling & Rendering Solution and Quantity Surveying, Rate Analysis & Cost Estimation at Federal Level ', 'Training on 3ds Max for 3D Design, Modeling & Rendering Solution and Quantity Surveying, Rate Analysis & Cost Estimation at Federal Level ', 3, '3560000', 50, 30, 10, '2022-01-21 11:47:59', 1, 1, 0, 24),
(30, 'Training on Project Management (Cost, Time & Quality Management) with the Help of Project Management Software at Federal Level ', 'Training on Project Management (Cost, Time & Quality Management) with the Help of Project Management Software at Federal Level ', 3, '356000', 20, 10, 5, '2022-01-21 11:48:33', 1, 1, 0, 24),
(31, 'Consultative Meeting & Performance Review', 'Consultative Meeting & Performance Review', 1, '', 0, 0, 0, '2022-01-21 11:48:54', 1, 1, 0, 1),
(32, 'Quarterly Consultative Meeting and Performance Review Sessions at Federal Level', 'Quarterly Consultative Meeting and Performance Review Sessions at Federal Level', 3, '25000', 20, 10, 5, '2022-01-21 11:49:56', 1, 1, 0, 31),
(33, 'Consultative Meeting with Federal & Regional Engineers on the Performance & Progress of Strategic Investments on a Quarterly Basis ', 'Consultative Meeting with Federal & Regional Engineers on the Performance & Progress of Strategic Investments on a Quarterly Basis ', 3, '3000', 30, 20, 10, '2022-01-21 11:50:42', 1, 1, 0, 31),
(34, 'Establish and Strengthen Rangeland Management Councils/Committee at Cluster and Woreda Levels', 'Establish and Strengthen Rangeland Management Councils/Committee at Cluster and Woreda Levels', 1, '', 0, 0, 0, '2022-01-21 11:51:11', 1, 1, 0, 1),
(35, 'Establishment of Rangeland Management Councils/Committees at Clusters ', 'Establishment of Rangeland Management Councils/Committees at Clusters ', 3, '300000', 30, 30, 10, '2022-01-21 11:52:20', 1, 1, 0, 34);

-- --------------------------------------------------------

--
-- Table structure for table `tbl_sub_component`
--

CREATE TABLE `tbl_sub_component` (
  `sub_component_id` int NOT NULL,
  `sub_component_name` varchar(200) NOT NULL,
  `sub_component_description` text NOT NULL,
  `sub_component_created_time` timestamp NULL DEFAULT NULL,
  `sub_component_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `component_id` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `tbl_sub_component`
--

INSERT INTO `tbl_sub_component` (`sub_component_id`, `sub_component_name`, `sub_component_description`, `sub_component_created_time`, `component_id`) VALUES
(1, 'Integrated Rangeland Management Planning – IRMP', 'Integrated Rangeland Management Planning – IRMP', '2021-10-06 22:20:48', 1),
(2, 'Supporting Strategic Investments', 'Supporting Strategic Investments', '2021-10-06 22:20:48', 1),
(3, 'Conflict Management & Secure Access to Key Resources', 'Conflict Management & Secure Access to Key Resources', '2021-10-14 09:16:58', 1),
(4, 'Enhancing Pastoral and Agro-Pastoral Production System', 'Enhancing Pastoral and Agro-Pastoral Production System', '2021-10-14 09:18:19', 2),
(5, 'Promoting livelihood Diversification & Market Access', 'Promoting livelihood Diversification & Market Access', '2021-10-14 09:18:34', 2),
(6, 'Provision of Basic Social Services and Infrastructure', 'Provision of Basic Social Services and Infrastructure', '2021-10-14 09:18:57', 3),
(7, 'Institutional Capacity Building Sub Component', 'Institutional Capacity Building Sub Component', '2021-10-14 09:19:06', 3),
(8, 'Knowledge Management, Research and Policy Support sub component', 'Knowledge Management, Research and Policy Support sub component', '2021-10-14 09:19:18', 3),
(9, 'Community Contribution ', 'Community Contribution ', '2021-10-14 09:19:29', 3),
(10, 'Project Management', 'Project Management', '2021-10-14 09:19:59', 4),
(11, 'Planning, Monitoring and Evaluation', 'Planning, Monitoring and Evaluation', '2021-10-14 09:20:27', 4);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_component`
--
ALTER TABLE `tbl_component`
  ADD PRIMARY KEY (`component_id`);

--
-- Indexes for table `tbl_llrp_activity`
--
ALTER TABLE `tbl_llrp_activity`
  ADD PRIMARY KEY (`llrp_activity_id`);

--
-- Indexes for table `tbl_sub_component`
--
ALTER TABLE `tbl_sub_component`
  ADD PRIMARY KEY (`sub_component_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_component`
--
ALTER TABLE `tbl_component`
  MODIFY `component_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `tbl_llrp_activity`
--
ALTER TABLE `tbl_llrp_activity`
  MODIFY `llrp_activity_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;
COMMIT;

我希望为每个组件列表及其子组件和活动首先实现其父组件和其子活动嵌套；有些有子活动有2/3的父级，而另一些则可能没有父级；如下所示

​

​

希望各位，现在你们明白我想要达到的目标了。欢迎任何提示或代码。

Answer 1

您可以使用递归cte检索活动，并将它们连接到子组件和组件中。将完整的层次结构存储在邻接列表中将使事情变得更容易，但我假设您的模式中还有其他依赖项。

WITH RECURSIVE activities AS
(
    SELECT sub_component_id, llrp_activity_id, llrp_activity_name, 0 AS depth, CAST(llrp_activity_id AS CHAR(200)) AS path
    FROM tbl_llrp_activity
    WHERE parent_llrp_activity_id = 0
    UNION ALL
    SELECT a1.sub_component_id, a1.llrp_activity_id, a1.llrp_activity_name, a2.depth+1, CONCAT(a2.path, ',', a1.llrp_activity_id)
    FROM tbl_llrp_activity a1
    JOIN activities a2 ON a2.llrp_activity_id = a1.parent_llrp_activity_id
)
SELECT c.*, sc.*, a.*
FROM activities a
JOIN tbl_sub_component sc ON a.sub_component_id = sc.sub_component_id
JOIN tbl_component c ON sc.component_id = c.component_id
ORDER BY path;

宠物皮毛

1. 避免调用表。相对于以表名作为列名的tbl_component.
2. Avoid，component或components肯定更可取。tbl_sub_component.sub_component_description.

比sub_component.description更简单、更清晰

更新

一个使用联合而不是连接来获取所有组件、子组件和活动的示例。我刚刚意识到component_id在UNIONed表中是可用的，所以我在三个UNIONed查询中的第一个删除了不必要的联接。我还添加了列号注释，因此很清楚什么是UNIONed。您可能需要在UNIONed查询中添加where子句-

WITH RECURSIVE activities AS
(
    SELECT
        /* 1 */ component_id,
        /* 2 */ sub_component_id,
        /* 3 */ llrp_activity_id,
        /* 4 */ llrp_activity_name,
        /* 5 */ llrp_activity_description,
        /* 6 */ 0 AS depth,
        /* 7 */ CAST(LPAD(llrp_activity_id, 4, '0') AS CHAR(200)) AS path
    FROM tbl_llrp_activity
    WHERE parent_llrp_activity_id = 0
    UNION ALL
    SELECT
        /* 1 */ a1.component_id,
        /* 2 */ a1.sub_component_id,
        /* 3 */ a1.llrp_activity_id,
        /* 4 */ a1.llrp_activity_name,
        /* 5 */ a1.llrp_activity_description,
        /* 6 */ a2.depth+1,
        /* 7 */ CONCAT(a2.path, ',', LPAD(a1.llrp_activity_id, 4, '0'))
    FROM tbl_llrp_activity a1
    JOIN activities a2 ON a2.llrp_activity_id = a1.parent_llrp_activity_id
)
(

    SELECT
        /* 1 */ 'activity' AS level,
        /* 2 */ path,
        /* 3 */ component_id,
        /* 4 */ sub_component_id,
        /* 5 */ llrp_activity_id,
        /* 6 */ llrp_activity_name,
        /* 7 */ llrp_activity_description
    FROM activities

) UNION ALL (

    SELECT
        /* 1 */ 'component' AS level,
        /* 2 */ -2 AS path,
        /* 3 */ c.component_id,
        /* 4 */ -1 AS sub_component_id,
        /* 5 */ -1 AS llrp_activity_id,
        /* 6 */ c.component_name,
        /* 7 */ c.component_description
    FROM tbl_component c

) UNION ALL (

    SELECT
        /* 1 */ 'sub-component' AS level,
        /* 2 */ -1 AS path,
        /* 3 */ sc.component_id,
        /* 4 */ sc.sub_component_id,
        /* 5 */ -1 AS llrp_activity_id,
        /* 6 */ sc.sub_component_name,
        /* 7 */ sc.sub_component_description
    FROM tbl_sub_component sc

) ORDER BY component_id, sub_component_id, path;

在构建HTML时应该添加“否”标签，因为这绝对是显示逻辑。表中也没有组件、子组件和活动的显式排序，因此我假设了基于ids和路径的排序。