如何在同一个数据库同一张表以某个字段为例,查出该字段的父子关系?

1.sys_page_config表结构:

表结构和数据语句: 

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50536
 Source Host           : 127.0.0.1:3306
 Source Schema         : wenxin

 Target Server Type    : MySQL
 Target Server Version : 50536
 File Encoding         : 65001

 Date: 16/08/2019 10:01:25
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_page_config
-- ----------------------------
DROP TABLE IF EXISTS `sys_page_config`;
CREATE TABLE `sys_page_config`  (
  `PAGEID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `PARENTID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `PAGE_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `PAGE_URL` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `PAGE_LEVEL` tinyint(4) DEFAULT NULL,
  `IS_VALID` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`PAGEID`) USING BTREE,
  CONSTRAINT `sys_page_config_ibfk_1` FOREIGN KEY (`PAGEID`) REFERENCES `sys_page_role` (`PAGEID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sys_page_config
-- ----------------------------
INSERT INTO `sys_page_config` VALUES ('016B43C4.C601.47CD.A39D.9F0700B9D202', '0', '系统配置', 'admin', 0, 1);
INSERT INTO `sys_page_config` VALUES ('0ABE06D4.74D0.4F6D.B699.5C180D6BEB3F', '0', '制作报告', 'report', 0, 1);
INSERT INTO `sys_page_config` VALUES ('600415B9.1BC3.45A8.BB23.E559A1EF14B7', '0', '查询统计', 'statis', 0, 1);
INSERT INTO `sys_page_config` VALUES ('790193C6.782C.40AD.A3FD.7132E88638F1', 'AE72AFF6.AFC1.449C.96F1.AF8E299E9DB3', '文章细览比对', 'topics/articles/detail', 2, 1);
INSERT INTO `sys_page_config` VALUES ('AE72AFF6.AFC1.449C.96F1.AF8E299E9DB3', 'BF7FA3D2.0A6D.4C3F.8A06.77BF306B4516', '文章转载情况', 'topics/articles', 1, 1);
INSERT INTO `sys_page_config` VALUES ('BF7FA3D2.0A6D.4C3F.8A06.77BF306B4516', '0', '版权传播', 'topics', 0, 1);
INSERT INTO `sys_page_config` VALUES ('C066BB6A.A8DA.440A.BCC4.7AA540BFE785', '0', '首页', 'index', 0, 1);
INSERT INTO `sys_page_config` VALUES ('C481C183.D24D.4A4A.B750.3F6C4FABD85D', '0', '转载检测', 'reprintsites', 0, 1);
INSERT INTO `sys_page_config` VALUES ('DCC602FF.62FA.4592.984D.C2DED017EBCC', '0', '临时检测', 'manual', 0, 1);

SET FOREIGN_KEY_CHECKS = 1;

2.需求

3.sql语句以及结果:

 SQL语句:

SELECT 
P0.PAGEID as P0_PAGEID,
P0.PARENTID as P0_PARENTID,
P0.PAGE_URL as P0_PAGE_URL,
P0.PAGE_NAME as P0_PAGE_NAME,
P0.PAGE_LEVEL as P0_PAGE_LEVEL,
P0.IS_VALID as P0_IS_VALID,

P1.PAGEID as P1_PAGEID,
P1.PARENTID as P1_PARENTID,
P1.PAGE_NAME as P1_PAGE_NAME,
P1.PAGE_URL as P1_PAGE_URL,
P1.PAGE_LEVEL as P1_PAGE_LEVEL,
P1.IS_VALID as P1_IS_VALID,

P2.PAGEID as P2_PAGEID,
P2.PARENTID as P2_PARENTID,
P2.PAGE_NAME as P2_PAGE_NAME,
P2.PAGE_URL as P2_PAGE_URL,
P2.PAGE_LEVEL as P2_PAGE_LEVEL,
P2.IS_VALID as P2_IS_VALID

FROM sys_page_config P0
LEFT JOIN sys_page_config P1 ON P0.PAGEID = P1.PARENTID
LEFT JOIN sys_page_config P2 ON P1.PAGEID = P2.PARENTID
WHERE P0.PARENTID = '0'  ;