我有一个包含所有类型数据的master_data，如Learning，Program和Course。另一个表mapping告诉您父/子关系。
这是集团/继承人名单
学习->程序->课程
一个学习可能有多个程序，一个程序可能有多个
课程。此外，一个程序可以是多种学习的一部分，并且
课程可以是多个程序的一部分。
如何通过保留一个额外的列（比如parent来标识父列）来获取数据，从而帮助对行进行分组。
主数据
id title description type
----------------------------------------------------------------
1 How to Present some info Learning
2 Securing Data more info Learning
3 Preparation plan more info Program
4 Protecting System info Program
5 Presentation mediums some info Program
6 know the importance some info Course
7 Notice the key concepts some info Course
8 Presenting in PPT some info Course
9 Presenting in Video format some info Course
10 Update the System some info Course
11 Chose a Strong password some info Course
映射
id learning_id program_id course_id
---------------- ----------- --------------
1 1 3 6
2 1 5 6
3 1 3 7
4 1 5 8
5 1 5 9
6 2 4 6
7 2 4 10
8 2 4 11
结果
id title description type parent
-------------------------------------------------------------------------
1 How to Present some info Learning 1 (itself)
3 Preparation plan more info Program 1
5 Presentation mediums some info Program 1
6 know the importance some info Course 3
7 Notice the key concepts some info Course 3
8 Presenting in PPT some info Course 5
9 Presenting in Video format some info Course 5
在这里，程序3，5是学习1的一部分。课程6、7属于课程3，8、9属于课程5
mysql中的上述查询
CREATE TABLE `master_data` (
`id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`description` TEXT NOT NULL,
`type` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO `master_data` (`id`, `title`, `description`, `type`) VALUES
('1', 'How to Present', 'some info', 'Learning'),
('2', 'Securing Data', 'few more info', 'Learning'),
('3', 'Preparation plan', 'informatoin abt this', 'Program'),
('4', 'Protecting System', 'security info', 'Program'),
('5', 'Presentation mediums', 'some info', 'Program'),
('6', 'You should know the importance', 'some info', 'Course'),
('7', 'Notice the key concepts', 'some info', 'Course'),
('8', 'Presenting in PPT', 'some info', 'Course'),
('9', 'Presenting in Vedio format', 'some info', 'Course'),
('10', 'Update the System', 'some info', 'Course'),
('11', 'Chose a Strong password', 'some info', 'Course');
CREATE TABLE `mapping` (
`id` INT NOT NULL AUTO_INCREMENT,
`learning_id` INT NOT NULL,
`program_id` INT NOT NULL,
`course_id` INT NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO `mapping` (`id`, `learning_id`, `program_id`, `course_id`) VALUES
('1', '1', '3', '6'),
('2', '1', '5', '6'),
('3', '1', '3', '7'),
('4', '1', '5', '8'),
('5', '1', '5', '9'),
('6', '2', '4', '6'),
('7', '2', '4', '10'),
('8', '2', '4', '11');
最佳答案
你也可以借助于联合条款：
select id,title,description,type,id from master_data where type='Learning'
UNION
select program_id,title,description,type,learning_id from master_data md, mapping m where md.id=m.program_id
UNION
select course_id,title,description,type,program_id from master_data md, mapping m where md.id=m.course_id;
为了保证数据的准确性和良好的性能，您应该有适当的约束和索引。
关于mysql - 如何使用映射表将表行映射到多个父行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47901177/