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`字段的交集检查，直到找到所有关联到当前用户权限的菜单节点。

DROP TABLE IF EXISTS "pisso_account" CASCADE; DROP SEQUENCE IF EXISTS "pisso_account_id_seq" CASCADE; CREATE SEQUENCE "pisso_account_id_seq"; grant select,update on sequence pisso_account_id_seq to lfgiapp; CREATE TABLE "pisso_account" ( "pisso_account_id" number(20) NOT NULL DEFAULT nextval('pisso_account_id_seq'::regclass), "sno" number(20) NOT NULL, "p10" varchar2(32) NOT NULL COLLATE "pg_catalog"."default", "p13" varchar2(100) COLLATE "pg_catalog"."default", "u_name" varchar2(32) COLLATE "pg_catalog"."default", "u_id" varchar2(32) COLLATE "pg_catalog"."default", "parent_group" varchar2(200) COLLATE "pg_catalog"."default", "child_group" varchar2(200) COLLATE "pg_catalog"."default", "branch_group" varchar2(200) COLLATE "pg_catalog"."default", "branch_group_code" varchar2(14) COLLATE "pg_catalog"."default", "begtime" number(11) NOT NULL, "endtime" number(11) NOT NULL, "branch" char(14) NOT NULL, CONSTRAINT "pisso_account_pkey" PRIMARY KEY ("pisso_account_id"), CONSTRAINT "pisso_account_p10_uni" UNIQUE ("p10") ); CREATE UNIQUE INDEX "pisso_account_uni_idx" ON "gi_dingtalk_resp"("sno"); COMMENT ON COLUMN "pisso_account"."pisso_account_id" IS '物理主键'; COMMENT ON COLUMN "pisso_account"."sno" IS '记录行号'; COMMENT ON COLUMN "pisso_account"."p10" IS 'P10账号'; COMMENT ON COLUMN "pisso_account"."p13" IS 'P13账号'; COMMENT ON COLUMN "pisso_account"."u_name" IS '员工姓名'; COMMENT ON COLUMN "pisso_account"."u_id" IS '身份证号'; COMMENT ON COLUMN "pisso_account"."parent_group" IS '总公司名称'; COMMENT ON COLUMN "pisso_account"."child_group" IS '分公司名称'; COMMENT ON COLUMN "pisso_account"."branch_group" IS '中支公司名称'; COMMENT ON COLUMN "pisso_account"."branch_group_code" IS '3级机构代码'; COMMENT ON COLUMN "pisso_account"."begtime" IS '开始日期'; COMMENT ON COLUMN "pisso_account"."endtime" IS '中止日期'; COMMENT ON COLUMN "pisso_account"."branch" IS '分公司代码'; COMMENT ON TABLE "pisso_account" IS 'P13及P10账号信息表'; 这个建表语句有问题吗？

这个建表语句中存在一些问题：

1. 数据类型：`varchar2` 是 Oracle 数据库特有的数据类型，而在 PostgreSQL 中应该使用 `varchar` 或 `text` 数据类型。
2. 主键：建表语句中的主键约束名 `pisso_account_pkey` 与索引名 `pisso_account_uni_idx` 重名，需要修改其中一个的名称。
3. 索引：建表语句中的索引 `pisso_account_uni_idx` 的表名应该是 `"pisso_account"` 而不是 `"gi_dingtalk_resp"`。
4. 注释：建表语句中的 `COMMENT ON` 语句需要修改，因为其中的表名和列名都是错误的。

以下是修改后的建表语句：

DROP TABLE IF EXISTS "pisso_account" CASCADE;
DROP SEQUENCE IF EXISTS "pisso_account_id_seq" CASCADE;

CREATE SEQUENCE "pisso_account_id_seq";

GRANT SELECT, UPDATE ON SEQUENCE "pisso_account_id_seq" TO lfgiapp;

CREATE TABLE "pisso_account" (
    "pisso_account_id" serial NOT NULL,
    "sno" bigint NOT NULL,
    "p10" varchar(32) NOT NULL,
    "p13" varchar(100),
    "u_name" varchar(32),
    "u_id" varchar(32),
    "parent_group" varchar(200),
    "child_group" varchar(200),
    "branch_group" varchar(200),
    "branch_group_code" varchar(14),
    "begtime" integer NOT NULL,
    "endtime" integer NOT NULL,
    "branch" char(14) NOT NULL,
    CONSTRAINT "pisso_account_pk" PRIMARY KEY ("pisso_account_id"),
    CONSTRAINT "pisso_account_p10_uni" UNIQUE ("p10")
);

CREATE UNIQUE INDEX "pisso_account_uni_idx" ON "pisso_account"("sno");

COMMENT ON TABLE "pisso_account" IS 'P13及P10账号信息表';
COMMENT ON COLUMN "pisso_account"."pisso_account_id" IS '物理主键';
COMMENT ON COLUMN "pisso_account"."sno" IS '记录行号';
COMMENT ON COLUMN "pisso_account"."p10" IS 'P10账号';
COMMENT ON COLUMN "pisso_account"."p13" IS 'P13账号';
COMMENT ON COLUMN "pisso_account"."u_name" IS '员工姓名';
COMMENT ON COLUMN "pisso_account"."u_id" IS '身份证号';
COMMENT ON COLUMN "pisso_account"."parent_group" IS '总公司名称';
COMMENT ON COLUMN "pisso_account"."child_group" IS '分公司名称';
COMMENT ON COLUMN "pisso_account"."branch_group" IS '中支公司名称';
COMMENT ON COLUMN "pisso_account"."branch_group_code" IS '3级机构代码';
COMMENT ON COLUMN "pisso_account"."begtime" IS '开始日期';
COMMENT ON COLUMN "pisso_account"."endtime" IS '中止日期';
COMMENT ON COLUMN "pisso_account"."branch" IS '分公司代码';

注意：由于我不知道该表的具体需求和使用场景，以上修改仅仅是基于语法方面的改进，实际建表时需要根据具体需求进行修改。