mysql - 如何只获取第一级深度子节点?
问题描述
我正在使用树存储。封闭表模式。
https://towardsdatascience.com/closure-table-pattern-to-model-hierarchies-in-nosql-c1be6a87e05b
如何获得没有孙子和曾孙的子节点?例如,从祖先 C,得到后代 G、H 和 F。
下面我以 Closure Table 数据库为例。
数据库关闭表
-- phpMyAdmin SQL Dump
-- version 5.1.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Aug 02, 2021 at 01:15 PM
-- Server version: 8.0.25
-- PHP Version: 8.0.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `closure_table`
--
-- --------------------------------------------------------
--
-- Table structure for table `category_name`
--
CREATE TABLE `category_name` (
`id` bigint NOT NULL,
`name` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;
--
-- Dumping data for table `category_name`
--
INSERT INTO `category_name` (`id`, `name`) VALUES
(1, 'Electronics'),
(2, 'Computers'),
(3, 'Accessories & Supplies'),
(4, 'Camera & Photo'),
(5, 'Computer accessories and peripherals'),
(6, 'Computer components'),
(7, 'Audio & Video Accessories'),
(8, 'Camera & Photo Accessories'),
(9, 'Accessories'),
(10, 'Bags & Cases'),
(11, 'Audio & Video Accessories'),
(12, 'Blank Media'),
(13, 'Computer screws'),
(14, 'Table Barebones'),
(15, '3D Glasses'),
(16, 'Antennas'),
(17, 'Accessory Bundles'),
(18, 'Bags & Cases'),
(19, 'Accessory Kits'),
(20, 'Bags & Cases'),
(21, 'Bag & Case Accessories'),
(22, 'Binocular Cases'),
(23, 'Computer headsets'),
(24, 'Computer microphones'),
(25, 'Discs BD-R'),
(26, 'Discs BD-RE'),
(27, 'bolts'),
(28, 'frame'),
(29, 'lamps'),
(30, 'coasters');
-- --------------------------------------------------------
--
-- Table structure for table `tree_path`
--
CREATE TABLE `tree_path` (
`children` bigint NOT NULL,
`parent` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;
--
-- Dumping data for table `tree_path`
--
INSERT INTO `tree_path` (`children`, `parent`) VALUES
(1, 1),
(3, 1),
(4, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(15, 1),
(16, 1),
(17, 1),
(18, 1),
(19, 1),
(20, 1),
(21, 1),
(22, 1),
(2, 2),
(5, 2),
(6, 2),
(11, 2),
(12, 2),
(13, 2),
(14, 2),
(23, 2),
(24, 2),
(25, 2),
(26, 2),
(27, 2),
(28, 2),
(29, 2),
(30, 2),
(3, 3),
(7, 3),
(8, 3),
(15, 3),
(16, 3),
(17, 3),
(18, 3),
(4, 4),
(9, 4),
(10, 4),
(19, 4),
(20, 4),
(21, 4),
(22, 4),
(5, 5),
(11, 5),
(12, 5),
(23, 5),
(24, 5),
(25, 5),
(26, 5),
(6, 6),
(13, 6),
(14, 6),
(27, 6),
(28, 6),
(29, 6),
(30, 6),
(7, 7),
(15, 7),
(16, 7),
(8, 8),
(17, 8),
(18, 8),
(9, 9),
(19, 9),
(20, 9),
(10, 10),
(21, 10),
(22, 10),
(11, 11),
(23, 11),
(24, 11),
(12, 12),
(25, 12),
(26, 12),
(13, 13),
(27, 13),
(28, 13),
(14, 14),
(29, 14),
(30, 14),
(15, 15),
(16, 16),
(17, 17),
(18, 18),
(19, 19),
(20, 20),
(21, 21),
(22, 22),
(23, 23),
(24, 24),
(25, 25),
(26, 26),
(27, 27),
(28, 28),
(29, 29),
(30, 30);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `category_name`
--
ALTER TABLE `category_name`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `tree_path`
--
ALTER TABLE `tree_path`
ADD PRIMARY KEY (`children`,`parent`),
ADD KEY `FK_PARENT` (`parent`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `category_name`
--
ALTER TABLE `category_name`
MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `tree_path`
--
ALTER TABLE `tree_path`
ADD CONSTRAINT `FK_CHILDREN` FOREIGN KEY (`children`) REFERENCES `category_name` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_PARENT` FOREIGN KEY (`parent`) REFERENCES `category_name` (`id`) ON DELETE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
解决方案
这是一个解决方案:
select p1.* from tree_path as p1
left outer join (tree_path as p2 join tree_path as p3 on p2.children = p3.parent)
on p2.parent = p1.parent
and p3.children = p1.children
and p2.parent <> p2.children
and p3.parent <> p3.children
where p1.parent = 3 and p2.parent is NULL;
+----------+--------+
| children | parent |
+----------+--------+
| 3 | 3 |
| 7 | 3 |
| 8 | 3 |
+----------+--------+
更改 p1.parent=7 并获得以下输出:
+----------+--------+
| children | parent |
+----------+--------+
| 7 | 7 |
| 15 | 7 |
| 16 | 7 |
+----------+--------+
它的工作方式如下:直接子代是后代,其中有从父节点到子节点的路径,但没有从父节点通过第三个节点到子节点的路径。所以我们尝试加入这样的路径(p2->p3),如果没有找到,那么 p2 和 p3 的所有列都将为 NULL。
推荐阅读
- c - 你能把 scanf 的输入分成两种不同的类型吗?
- web-component - Webfundamentals、带有 shadowDOM 的 CustomElement 和带有 HTML 导入的 HTML 模板
- powershell - 将文件从子文件夹复制到父文件夹并用父文件夹名称重命名文件
- github - git 警告 - “您似乎克隆了一个空存储库” - 从 github 克隆到本地时
- window - 电子应用程序始终以全角放置在顶部,其他窗口应放置在其下方
- javascript - 如何判断 JavaScript 中的图形对象是隐藏的还是可见的?(不是 jQuery)
- ruby-on-rails - 使用acts_as_commentable 和acts_as_votable ROR
- c# - 如何在 SQL Server 中插入/更新信息
- android - 我应该在android中实现哪个依赖注入
- docker - 嵌套 docker 设置:子暴露父