用ID/ParentID设计的Treeview结构表,若需要复制一份完整的Treeview, 要用逻辑代码递归实现,处理id/pid有点麻烦,性能也不好, 若可用简单的几句sql实现,则可以提高性能 使用postgresql 测试通过,并使用connectby取整颗树(可使用trid取,但无结构,如Level)
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 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";
用WITH AS的没有测试成功过,请大侠指点!
--结束END--
本文标题: Treeview结构的表资料复制(Treeview Table COPY)
本文链接: https://www.lsjlt.com/news/8368.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-13
2024-05-13
2024-05-13
2024-05-13
2024-05-12
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0