java - 如何从表中删除树节点及其子记录(无级联删除)？

Question

我在 postgreSQL v9.1 中有这个表:

CREATE TABLE ad_treenodemm
(
  ad_tree_id numeric(10,0) NOT NULL,
  node_id numeric(10,0) NOT NULL,
  ad_client_id numeric(10,0) NOT NULL,
  ad_org_id numeric(10,0) NOT NULL,
  name character varying(60) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  parent_id numeric(10,0),
  seqno numeric(10,0),
  CONSTRAINT ad_treenodemm_pkey PRIMARY KEY (ad_tree_id , node_id ),
  CONSTRAINT adtree_adtreenodemm FOREIGN KEY (ad_tree_id)
      REFERENCES adempiere.ad_tree (ad_tree_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT ad_treenodemm_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
)

重要栏目说明:
* ad_tree_id = 树组id(连接到ad_tree表)
* node_id = 节点 ID
* parent_id = 父节点id(如果为0 =>表示该节点在最上面)

其余列可以忽略。

例如，我有这样的 ad_treenodemm 表数据表示:

# Group1 (all node belows are assigned with ad_tree_id=1001)
    -Accounting (node_id=101, parent_id=0)
        -Costing (node_id=202, parent_id=101)
            -Cost Type (node_id=103, parent_id=202)
            -Cost Element (node_id=24, parent_id=202)
        -Client Accounting Processor (node_id=105, parent_id=101)
        -Reset Accounting (node_id=6, parent_id=101)
            ...

    -Finance (node_id=4110, parent_id=0)
        ...

# Group2 (all node belows are assigned with ad_tree_id=1002)
    ...

比方说，我想删除 Group1 中的 Accounting 节点及其子节点。这意味着，它还会删除节点:成本核算、成本类型、成本要素、重置会计等。怎么做？

解决方案可以使用带有 JDBC 的 SQL 或 Java 语言(但如果可能，最好使用 SQL)。

更新:我找到了一个使用 WITH RECURSIVE (CTE) sql 的解决方案，但是它不太优雅:

WITH RECURSIVE temp(ad_tree_id, node_id, parent_id) AS (
    SELECT a.ad_tree_id, a.node_id, a.parent_id
    FROM ad_treenodemm a 
    WHERE ad_tree_id=1001 AND node_id=101      -- look at this

    UNION ALL

    SELECT b.ad_tree_id, b.node_id, b.parent_id
    FROM ad_treenodemm b
    INNER JOIN temp c on c.node_id = b.parent_id
    WHERE b.ad_tree_id=c.ad_tree_id
)
DELETE FROM ad_treenodemm a
WHERE (a.ad_tree_id, a.node_id) IN (
    SELECT ad_tree_id, node_id FROM temp
);

您看到我将参数 (WHERE ad_tree_id=1001 AND node_id=101) 放在 WITH 子句中。有人知道如何通过将参数语句放在 WITH 子句之外来改进 SQL 吗？

对于想要在不删除记录的情况下试验查询的任何人，请使用:

WITH RECURSIVE temp(ad_tree_id, node_id, parent_id) AS (
    SELECT a.ad_tree_id, a.node_id, a.parent_id
    FROM ad_treenodemm a 
    WHERE ad_tree_id=1001 AND node_id=101

    UNION ALL

    SELECT b.ad_tree_id, b.node_id, b.parent_id
    FROM ad_treenodemm b
    INNER JOIN temp c on c.node_id = b.parent_id
    WHERE b.ad_tree_id=c.ad_tree_id
)
SELECT * FROM ad_treenodemm a
WHERE (a.ad_tree_id, a.node_id) IN (
    SELECT ad_tree_id, node_id FROM temp
)
ORDER BY a.parent_id, a.node_id

Answer 1

因为您不想“改变表结构”，所以您可以使用 recursive query无论如何(或递归执行工作的函数)。因为添加 FK 约束符合“改变表结构”的条件。

如果没有这些限制，最优雅的解决方案是修复您提到的 NULL 值并添加 NOT NULL constraint到专栏。 然后添加 FK constraint如评论中所述，使用 ON DELETE CASCADE，首先由 @lc 完成。

递归查询

DELETE 具有可写 CTE 可能如下所示:

WITH RECURSIVE x AS (
   SELECT ad_tree_id, node_id
   FROM   ad_treenodemm
   WHERE (ad_tree_id, node_id) = (1,5)   -- enter dead node walking here

   UNION  ALL
   SELECT a.ad_tree_id, a.node_id
   FROM   x
   JOIN   ad_treenodemm a ON a.parent_id = x.node_id
   )
DELETE FROM ad_treenodemm a
USING  x
WHERE (a.ad_tree_id, a.node_id) = (x.ad_tree_id, x.node_id)

要一步完成此操作，您需要 PostgreSQL 9.1 或更高版本 data-modifying CTEs .否则，您必须运行单独的 SELECT 来收集行，然后再运行 DELETE。

-> Live demo @sqlfiddle