postgresql数据库表结构 id,parent_id,name parent_id关联id 查询时 parent_id > 0 则不显示parent_id关联的那条父级记录

可以使用以下 SQL 语句实现：

SELECT t1.*
FROM your_table t1
LEFT JOIN your_table t2 ON t1.parent_id = t2.id
WHERE t1.parent_id = 0 OR t2.parent_id IS NULL;

这里使用了自连接的方式，将表自身连接两次，然后在 WHERE 子句中过滤出 parent_id 为 0 或者其对应的父级记录不存在的记录。其中，t2.parent_id IS NULL 是为了处理 parent_id 字段为 NULL 的情况。

需要注意的是，这种方式比较消耗性能，如果数据量比较大，建议在表中增加一个字段记录每条记录的层级关系，这样查询的性能会更好。

相关问题

使用postgresql存储菜单menu表，有字段menu_id和数组类型字段parent_menu_ids，表示一个子菜单可以放到0个或多个菜单下，菜单为多级嵌套，用户有子菜单的权限时，要显示完整的菜单树，如何根据用户拥有的子菜单权限列表查询出该用户可见的整个菜单

在PostgreSQL中，为了实现多级嵌套菜单并根据用户权限查询其可见的菜单，你可以创建一个包含菜单ID和父菜单ID数组的`menu`表，并且在查询时使用`jsonb`数据类型和相关的函数。以下是操作步骤：

1. 定义菜单表结构：

CREATE TABLE menu (
    menu_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_menu_ids JSONB[] NOT NULL,
    permission JSONB -- 存储用户的权限信息，如JSON对象 { "user_id": ..., "permissions": [menu_id1, menu_id2, ...] }
);

2. 当用户获得新的子菜单权限时，更新`permission`字段。例如，如果用户获得了`menu_id3`的权限：

UPDATE menu SET permission = permission || '{"user_id": user_id, "permissions": ["menu_id3"]}'::JSONB WHERE menu_id IN (SELECT * FROM permissions WHERE user_id = 'your_user_id');

这里假设`permissions`是一个存储了所有用户权限的临时表。

3. 根据用户的权限查询菜单树：

WITH RECURSIVE menu_tree AS (
    SELECT 
        m.*,
        ARRAY[NULL]::JSONB AS ancestors
    FROM 
        menu m
    WHERE 
        permission->>'permissions' @> '{"user_id": '||'your_user_id'||'}'
    
    UNION ALL
    
    SELECT 
        m.*, 
        array_append(menu_tree.ancestors, m.menu_id) AS ancestors
    FROM 
        menu m
        JOIN menu_tree ON m.parent_menu_ids && menu_tree.ancestors
)
SELECT * FROM menu_tree;

这个递归查询会查找所有的直接子菜单，并通过`parent_menu_ids`字段的交集检查，直到找到所有关联到当前用户权限的菜单节点。

在 PostgreSQL 中，可以使用递归查询（Common Table Expressions，CTEs）来处理树状结构的数据，比如你提到的区域划分表 sys_area_division。下面是一个示例，展示如何从这个表中查询省、市、县、乡的信息，并将它们组合成四列。

在 PostgreSQL 中，使用递归 Common Table Expressions (CTE) 来处理树状结构数据非常方便。假设有一个名为 `sys_area_division` 的表，它包含字段如 `id`, `parent_id` 和 `division_name`，表示区域之间的层级关系。我们可以创建一个 CTE 来模拟 "自连接" 查询，递归地获取所有下级区域。

WITH RECURSIVE area_hierarchy AS (
    -- 定义初始基础记录，通常是根节点
    SELECT id, division_name, parent_id, 0 as level
    FROM sys_area_division
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归部分，根据 parent_id 联接上级记录并增加层级
    SELECT child.id, child.division_name, child.parent_id, parent.level + 1
    FROM sys_area_division AS child
        JOIN area_hierarchy AS parent ON child.parent_id = parent.id
)
SELECT * FROM area_hierarchy;

这个查询会返回一个结果集，每一行代表一个区域及其相应的层级信息（level）。通过调整 `UNION ALL` 后面的子查询，你可以选择输出所需的层级（例如只取省级、市级等），只需添加合适的 WHERE 子句限制 `level` 参数即可。