大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说Treeview结构的表资料复制(Treeview Table COPY),希望您对编程的造诣更进一步.
用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");
- SQL
--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";
用WITH AS的没有测试成功过,请大侠指点!
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6250.html
