我在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)
...
型
比如我想删除集团1中的【核算】节点及其子节点,也就是说同时删除了成本计算、成本类型、成本要素、重置核算等节点,如何操作?
解决方案可以是SQL或Java语言与JDBC(但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
型
1条答案
按热度按时间vfh0ocws1#
添加一个FK约束会被定义为“改变表结构”,因为你不想这样,我们就用一个recursive query或者一个递归函数来完成这项工作。
如果没有这样的限制,最优雅的解决方案是固定您提到的
null
值,并向列添加NOT NULL
约束。* 然后 * 添加FK约束,如注解中提到的ON DELETE CASCADE
,首先是lc。递归查询
DELETE
具有“可写”CTE:字符串
数据修改CTE需要PostgreSQL 9.1或更高版本。否则您必须运行单独的
SELECT
来收集行,然后运行DELETE
。fiddle的
老小提琴