PostgreSQL从表中获取父类别 [英] PostgreSQL get parent categories from table
问题描述
我有如下表。
CREATE TABLE my.categories (id bigint, parent_id bigint, name varchar(128));
INSERT INTO my.categories (id, parent_id, name) VALUES (1, null, 'LEVEL 1');
INSERT INTO my.categories (id, parent_id, name) VALUES (2, 1, 'LEVEL 2.1');
INSERT INTO my.categories (id, parent_id, name) VALUES (3, 1, 'LEVEL 2.2');
INSERT INTO my.categories (id, parent_id, name) VALUES (4, 2, 'LEVEL 3.1.1');
INSERT INTO my.categories (id, parent_id, name) VALUES (5, 2, 'LEVEL 3.1.2');
INSERT INTO my.categories (id, parent_id, name) VALUES (6, 3, 'LEVEL 3.2.1');
+----+-----------+---------------+
| id | parent_id | name |
+----+-----------+---------------+
| 1 | null | 'LEVEL 1' |
| 2 | 1 | 'LEVEL 2.1' |
| 3 | 1 | 'LEVEL 2.2' |
| 4 | 2 | 'LEVEL 3.1.1' |
| 5 | 2 | 'LEVEL 3.1.2' |
| 6 | 3 | 'LEVEL 3.2.1' |
+----+-----------+---------------+
我需要获取父类别的所有ID。
I need to get all id's for parent categories.
WITH RECURSIVE tree(theId) AS (
SELECT id
FROM my.categories
WHERE id = theId -- wrong here, because its not a procedure
UNION ALL
SELECT table1.id
FROM my.categories AS table1
JOIN tree AS parent ON theId = table1.parent_id
)
SELECT DISTINCT theId FROM tree WHERE theId = 6;
带有数据的示例结果,但实际上我只需要id。
Example result with data but actually I need only id's.
+----+-----------+---------------+
| id | parent_id | name |
+----+-----------+---------------+
| 1 | null | 'LEVEL 1' |
| 3 | 1 | 'LEVEL 2.2' |
| 6 | 3 | 'LEVEL 3.2.1' |
+----+-----------+---------------+
或类似这样:
+----+-----------+---------------+
| id | parent_id | name |
+----+-----------+---------------+
| 3 | 1 | 'LEVEL 2.2' |
| 6 | 3 | 'LEVEL 3.2.1' |
+----+-----------+---------------+
问题是我不允许使用程序。此查询应用作许多其他查询的子查询。并且请不要看 name
列无关紧要。
The trouble is I'm not allowed to use procedures. This query should be used as sub-query for many other queries. And please dont look at name
column it is irrelevant.
推荐答案
如果我明白了,这就是您所需要的。
If I get you, this is what you need.
首先,通过以下查询,您可以获得所有父代ID:
First, with the folowing query you can get all the parent ids:
WITH RECURSIVE t(id, parentlist) AS (
SELECT id, ARRAY[]::bigint[] FROM my.categories WHERE parent_id IS NULL
UNION
SELECT my.categories.id, my.categories.parent_id || t.parentlist
FROM my.categories
JOIN t ON categories.parent_id = t.id
) SELECT * FROM t
-- outputs:
-- id | parentlist
-- ----+------------
-- 1 | {}
-- 2 | {1}
-- 3 | {1}
-- 4 | {2,1}
-- 5 | {2,1}
-- 6 | {3,1}
如果您想获得一个同名父母的记录,您只需要更改查询,例如:
If you want to get a record of the parents of one id you just need to change the query like:
WITH RECURSIVE t(id, parentlist) AS (
SELECT id, ARRAY[]::bigint[] FROM my.categories WHERE parent_id IS NULL
UNION
SELECT my.categories.id, my.categories.parent_id || t.parentlist
FROM my.categories
JOIN t ON categories.parent_id = t.id
) SELECT unnest(parentlist) as parents_ids FROM t WHERE id=6;
-- outputs:
-- parents_ids
-- -----------
-- 3
-- 1
请注意,最后一个查询不会输出当前 id(6)。
Note that the last query does not output the "current" id (6).
这篇关于PostgreSQL从表中获取父类别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!