获取父子层次结构 [英] Getting Parent Child hierarchy

问题描述

我正在尝试将一个孩子(狗)的祖先提高到5级.例如，在所附图片中，我将发送"Spencer di Casa Massarelli"，结果希望有相关的父母(父亲和母亲) .在我的数据库结构中，我使用了mother_id和mother_id.

I'm trying to get ancestors of a child (dog) upto Level 5. For Example in attached picture I'll be sending "Spencer di Casa Massarelli" and in result want to have associated parents (both father and mother). In my DB structure I've used father_id and mother_id.

DB&版本:10.4.11-MariaDB

DB & version: 10.4.11-MariaDB

表脚本:

CREATE TABLE `dogs` (
  `dog_id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `father_id` int(11) DEFAULT NULL,
  `moter_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`dog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `dogs` VALUES ('0', null, null, null);
INSERT INTO `dogs` VALUES ('1', 'Father', null, null);
INSERT INTO `dogs` VALUES ('2', 'Mother', null, null);
INSERT INTO `dogs` VALUES ('3', 'Father1', null, null);
INSERT INTO `dogs` VALUES ('4', 'Mother2', null, null);
INSERT INTO `dogs` VALUES ('5', 'Son', '1', '2');
INSERT INTO `dogs` VALUES ('6', 'Daughter', '3', '4');
INSERT INTO `dogs` VALUES ('7', 'GrandSon', '5', '6');

我尝试了以下自联接查询，但问题是我无法找到合适的父母，即，第一个父母的父母(父亲和母亲).

I've tried following self join query but the problem is I'm unable to get right parents i.e., parents(both father and mother) of first parent.

SELECT t1.name AS lev1, 
       t2.name AS lev2Father, 
       t3.name AS lev2Mother, 
       t4.name AS level3Father, 
       t5.name AS level3Mother, 
       t6.name AS level4Father, 
       t7.name AS level4Mother, 
       t8.name AS level5Father, 
       t9.name AS level5Mother, 
       t10.name AS level6Father, 
       t11.name AS level6Mother 
FROM dogs AS t1 
LEFT JOIN dogs AS t2 ON t2.dog_id = t1.father_id 
LEFT JOIN dogs AS t3 ON t3.dog_id = t1.mother_id 
LEFT JOIN dogs AS t4 ON t4.dog_id = t2.father_id 
LEFT JOIN dogs AS t5 ON t5.dog_id = t2.mother_id 
LEFT JOIN dogs AS t6 ON t6.dog_id = t4.father_id 
LEFT JOIN dogs AS t7 ON t7.dog_id = t4.mother_id 
LEFT JOIN dogs AS t8 ON t8.dog_id = t6.father_id 
LEFT JOIN dogs AS t9 ON t9.dog_id = t6.mother_id 
LEFT JOIN dogs AS t10 ON t10.dog_id = t8.father_id 
LEFT JOIN dogs AS t11 ON t11.dog_id = t8.mother_id 
WHERE t1.dog_id = 7

推荐答案

WITH RECURSIVE
cte AS (
SELECT *, 0 level, '      ' relation
FROM dogs
WHERE dog_id = 7

UNION ALL

SELECT dogs.*, level + 1, 'father'
FROM dogs
JOIN cte ON cte.father_id = dogs.dog_id
WHERE level < 5

UNION ALL

SELECT dogs.*, level + 1, 'mother'
FROM dogs
JOIN cte ON cte.mother_id = dogs.dog_id
WHERE level < 5
)
SELECT *
FROM cte
ORDER BY level, relation;

小提琴

结果

dog_id | name     | father_id | mother_id | level | relation
-----: | :------- | --------: | --------: | ----: | :-------
     7 | GrandSon |         5 |         6 |     0 |         
     5 | Son      |         1 |         2 |     1 | father  
     6 | Daughter |         3 |         4 |     1 | mother  
     1 | Father   |      null |      null |     2 | father  
     3 | Father1  |      null |      null |     2 | father  
     2 | Mother   |      null |      null |     2 | mother  
     4 | Mother2  |      null |      null |     2 | mother  

这篇关于获取父子层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！