使用递归CTE确定一条记录是否为另一条记录的后代 [英] Determine if a record is a descendant of another record using recursive CTE

问题描述

下表存储了说明手册中的主题。除了作为根的1的记录id之外，每个主题都有一个父主题。该应用程序允许更改除根主题之外的所有主题的父级，并且我将需要防止将祖先分配为其自身的后代。

例如，记录#2的父级不应从1更改为7。

如何确定一条记录是否为另一条记录的后代？我不愿对后代的数量进行"硬编码"。请注意，我实际上使用的是MariaDB。

CREATE TABLE IF NOT EXISTS `app`.`manual` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `content` TEXT NULL,
  `listorder` TINYINT NULL,
  `keywords` TEXT NULL,
  `display_list` TINYINT NULL,
  `parent_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_manual_manual1_idx` (`parent_id` ASC),
  CONSTRAINT `fk_manual_manual1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `app`.`manual` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

id   parent_id
1    null
2    1
3    2
4    3
5    4
6    5
7    6

推荐答案

如果id为6的记录是id为2的记录的后代，则以下查询将返回大于零的计数。

WITH RECURSIVE ancestors AS (
SELECT * FROM manual WHERE id=2
UNION
SELECT m.*
FROM manual m
INNER JOIN ancestors a ON a.id=m.parent_id
)
SELECT COUNT(*) FROM ancestors WHERE id=6;

这篇关于使用递归CTE确定一条记录是否为另一条记录的后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！