通过父类别id获取父类别

Question

我有以下类别数据库结构

+----+---------------+-----------+
| id | category_name | parent_id |
+----+---------------+-----------+
| 1  | Category One  | 0         |
| 2  | Category Two  | 1         |
| 3  | Category Three| 1         |
| 4  | Category Four | 1         |
| 5  | Category Five | 1         |
| 6  | Category Six  | 1         |
+----+---------------+-----------+

我有一些变量包含一个子id，例如parent_id =1的category_id =4

我想得到所有的父子类别，包括它自己4

我没有家长ID，只有我需要的parent_category的category_id

到目前为止，我所做的

SELECT child.*
FROM `ec_categories` AS `child`
LEFT JOIN `ec_categories` AS `parent` ON `child`.`parent_id`=`parent`.`id`
WHERE `child`.`id` = '4'

有没有一种方法可以使用1个查询来获取它们？

Answer 1

假设您有以下架构：

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    name varchar(25) NOT NULL, 
    parent_id INT
);
    
INSERT INTO categories (name, parent_id)
VALUES
    ('Cat 1', 0),
    ('Cat 2', 1),
    ('Cat 3', 1),
    ('Cat 4', 2),
    ('Cat 5', 3);

然后，您可以使用recursive common table expression

WITH RECURSIVE parents AS
  ( SELECT id, name, parent_id
    FROM categories
    WHERE id = 4 -- 4 as an example here
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN parents p
    ON p.parent_id = c.id
  ) 
  
SELECT * FROM parents;

结果：

+----+-------+-----------+
| id | name  | parent_id |
+----+-------+-----------+
|  4 | Cat 4 |         2 |
|  2 | Cat 2 |         1 |
|  1 | Cat 1 |         0 |
+----+-------+-----------+

您需要一个最新版本的MariaDB/MySql

Answer 2

如果您在内部查询方面没有问题，那么可以使用

SELECT * FROM categories where parent_id=(SELECT parent_id FROM categories where id=4);