主页 / dba / 问题 / 290276
Accepted
HTF
Asked: 2021-04-23 03:37:55 +0800 CST

使用父名获取父级和子级

  • 772

有没有办法使用单个查询获取父母及其子女？

CREATE TABLE `servers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO servers (name) VALUES ('kvm01'), ('kvm02')

INSERT INTO servers (name, parent_id) VALUES ('web01', 1), ('db01', 1), ('web02', 2), ('db02', 2)

电流输出：

mysql [lab]> SELECT child.id FROM servers AS parent LEFT JOIN servers AS child ON (child.parent_id = parent.id) WHERE parent.name = 'kvm01';
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

预期输出：

+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
+------+
mysql query

2 个回答

  1. Best Answer

    您可以调整您的加入条件

    SELECT child.id
  FROM servers AS parent 
  LEFT JOIN servers AS child
    ON (child.parent_id = parent.id
     OR child.id = parent.id)
  WHERE parent.name = 'kvm01';

    或使用UNION/UNION ALL分别检索父记录：

    SELECT child.id
  FROM servers AS parent 
  LEFT JOIN servers AS child
    ON child.parent_id = parent.id
  WHERE parent.name = 'kvm01'
UNION ALL
SELECT parent.id
  FROM servers AS parent
  WHERE parent.name = 'kvm01';
    • 4 
  2. SELECT t2.*
FROM servers t1
LEFT JOIN servers t2 ON t1.id IN (t2.id, t2.parent_id)
WHERE t1.name = 'kvm01';

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f41816149af8751cf109a4e1c0a249e

    • 3

相关问题