Treeview结构的表资料复制(TreeviewTableCOPY)

Z时代
2024-01-10
分类：综合

database

用ID/ParentID设计的Treeview结构表,若需要复制一份完整的Treeview, 要用逻辑代码递归实现,处理id/pid有点麻烦,性能也不好, 若可用简单的几句SQL实现,则可以提高性能使用Postgresql 测试通过,并使用connectby取整颗树(可使用trid取,但无结构,如Level)

table

CREATE TABLE public.tmp_treeview (
	tid serial NOT NULL DEFAULT nextval("tmp_treeview_tid_seq"::regclass),
	tname text NULL,
	tpid int4 NULL,
	toid int4 NULL,
	tseq int4 NULL DEFAULT 1,
	trid varchar NULL,
	CONSTRAINT tmp_treeview_pkey PRIMARY KEY (tid)
);
--toid为临时字段名
--trid为区分根系
--准备数据
INSERT INTO public.tmp_treeview
(tid, tname, tpid, toid, tseq, trid)
VALUES(1, "L0", NULL, NULL, 1, "1");
INSERT INTO public.tmp_treeview
(tid, tname, tpid, toid, tseq, trid)
VALUES(2, "L1", 1, NULL, 1, "1");
INSERT INTO public.tmp_treeview
(tid, tname, tpid, toid, tseq, trid)
VALUES(3, "L2-1", 2, NULL, 1, "1");
INSERT INTO public.tmp_treeview
(tid, tname, tpid, toid, tseq, trid)
VALUES(4, "L2-1", 2, NULL, 2, "1");
INSERT INTO public.tmp_treeview
(tid, tname, tpid, toid, tseq, trid)
VALUES(5, "L3", 4, NULL, 1, "1");
INSERT INTO public.tmp_treeview
(tid, tname, tpid, toid, tseq, trid)
VALUES(6, "L4", 5, NULL, 1, "1");

--sql1
INSERT INTO tmp_treeview(
	tname,
	tpid,
	toid,
	tseq,
	trid)
SELECT t2.tname,t2.tpid,t2.tid,t2.tseq,"3" FROM (
	SELECT * FROM connectby("tmp_treeview", "tid","tpid", "tseq", "1", 0 )
			AS t(tid int,tpid int,level int,tseq int)
) t1
INNER JOIN tmp_treeview t2 ON t1.tid = t2.tid
AND t2.trid = "1"
--sql2
UPDATE tmp_treeview t1 SET tpid = t2.tid FROM (
	SELECT tt1.tid,tt2.tid AS tid2 FROM tmp_treeview tt1 
	INNER JOIN tmp_treeview tt2 ON tt1.toid = tt2.tid 
	AND tt1.trid = "3"
) t2
WHERE t1.tpid = t2.tid2 AND t1.trid = "3";

WITH AS(未测试通过)

WITH copy_tree AS (
INSERT INTO tmp_treeview(
	tname,
	tpid,
	toid,
	tseq,
	trid)
SELECT t2.tname,t2.tpid,t2.tid,t2.tseq,"3" FROM (
	SELECT * FROM connectby("tmp_treeview", "tid","tpid", "tseq", "1", 0 )
			AS t(tid int,tpid int,level int,tseq int)
) t1
INNER JOIN tmp_treeview t2 ON t1.tid = t2.tid
AND t2.trid = "1" RETURNING *
)
UPDATE tmp_treeview t1 SET tpid = t2.tid FROM (
	SELECT tt1.tid,tt2.tid AS tid2 FROM tmp_treeview tt1 
	INNER JOIN copy_tree tt2 ON tt1.toid = tt2.tid 
	AND tt1.trid = "3"
) t2
WHERE t1.tpid = t2.tid2 AND t1.trid = "3";