MySQL版本8.0 模式SQL
CREATE TABLE IF NOT EXISTS `department` (
`id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`father` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_department_department_idx` (`father` ASC) VISIBLE,
CONSTRAINT `fk_department_department`
FOREIGN KEY (`father`)
REFERENCES `department` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
insert into department (id,name,father)
values
(1, 'dp1',null),
(2, 'dp2',null),
(3, 'dp3',1),
(4, 'dp4',1),
(5, 'dp5',2),
(6, 'dp6',4),
(7, 'dp7',6),
(8, 'dp8',6),
(9, 'dp9',6);
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';
我的查询:
WITH RECURSIVE cte_department AS (
SELECT
d1.id,
d1.name,
d1.father
FROM
department d1
WHERE
d1.id=6
UNION ALL
SELECT
d2.id,
d2.name,
d2.father
FROM
department d2
INNER JOIN cte_department cte ON cte.id = d2.father
)
SELECT * FROM cte_department;
结果:
id name father
6 dp6 4
7 dp7 6
8 dp8 6
9 dp9 6
我需要什么:
id name father
1 dp1 null
4 dp4 1
6 dp6 4
7 dp7 6
8 dp8 6
9 dp9 6
问题是:我可以获取所有子元素,但我需要在此查询中添加给定ID的所有父元素,例如ID 6。我陷入了困境。如果有人能帮我,请参考fiddle链接。
UNION ALL
,即使我使用括号也无济于事。我必须将你的“Up”和“Down”查询合并为一个Up/Down查询。很棒的是MySQL允许在这里使用UNION ALL
来让查询非常易读。感谢您的分享。 - Thorsten Kettner