数据库表中的多级用户 [英] Multilevel Users in the Database table

问题描述

我有一个表用户，其中一个字段为 id，另一个字段为 parent id。我也希望用户表中有目标字段。

I am having one table users in which I have one field 'id' and another field is 'parent id'. Also I have expected target field in the users table.

我有直到第8级层次结构的用户列表。

I am having list of users till the 8th level hierarchy. Where A is parent of B and B is parent of C and so on.

例如

A   level 0
|
B   level 1
|
c   level 2

现在，当我在寻找用户A时，我想获得全部子用户使用sql查询预期目标。
，即当我使用id = A的id时，我可以看到A，B，C等的预期目标。

Now when I am looking for user A. I want to get the all the sub users using sql query 'expected target'. i.e. When I use id = id of A then I can see the expected target of A,B,C etc.

如果A，B和C分别为1000、500和200，输出应为：

If expected_targets for A, B and C are 1000, 500 , 200 respectively the output should be like :

id      parent_id        expected_target

A_id                        1000  
B_id        A_id            500  
C_id        B_id            200 

推荐答案

SET search_path='tmp';

DROP TABLE targets CASCADE;
CREATE TABLE targets
        ( id integer not null primary key
        , parent_id integer references targets(id)
        , expected_target integer
        );

INSERT INTO targets(id,parent_id,expected_target) VALUES
(1,NULL, 1000), (2,1, 500), (3,2, 200);


WITH RECURSIVE zzz AS (
        SELECT t0.id, t0.parent_id
        , 0::integer AS level
        , t0.expected_target
        FROM targets t0
        WHERE t0.parent_id IS NULL
        UNION
        SELECT t1.id, t1.parent_id
        , 1+zzz.level AS level
        , t1.expected_target
        FROM targets t1
        JOIN zzz ON zzz.id = t1.parent_id
        )
SELECT * FROM zzz
        ;

输出：

SET
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "targets_pkey" for table "targets"
CREATE TABLE
INSERT 0 3
 id | parent_id | level | expected_target 
----+-----------+-------+-----------------
  1 |           |     0 |            1000
  2 |         1 |     1 |             500
  3 |         2 |     2 |             200
(3 rows)

更新：如果您不想整棵树，真正的树，只剩下一棵树，只不过是其中的一部分，您当然可以稍微改变条件：

UPDATE: if you don't want the whole tree, the true tree and nothing but the tree but only a subtree part of it, you can of course change the conditions a bit:

WITH RECURSIVE zzz AS (
        SELECT t0.id, t0.parent_id
        , 0::integer AS level
        , t0.expected_target
        FROM targets t0
        -- WHERE t0.parent_id IS NULL
        WHERE t0.id = 2
        UNION
        SELECT t1.id, t1.parent_id
        , 1+zzz.level AS level
        , t1.expected_target
        FROM targets t1
        JOIN zzz ON zzz.id = t1.parent_id
        )
SELECT * FROM zzz
        ;

这篇关于数据库表中的多级用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！