假设-我有下面的表结构
elementid, parentid, elementtitle, sortorder
160 0 Brand New Tutorial 1
161 160 Brand New Tutorial New Step 1
168 5 Tutorial Topic 1.1 1
171 168 Tutorial Topic 1.1.1 1
172 171 Tutorial Topic 1.1.1.1 1
以下是生成树的常规SQL:
WITH menu AS
(
SELECT parentid, elementid, elementtitle, sortorder FROM dbo.ta_tutorial_elements WHERE (elementid = @eid)
UNION ALL
SELECT e.parentid, e.elementid, e.elementtitle, e.sortorderFROM dbo.ta_tutorial_elements AS e INNER JOIN menu AS m ON e.parentid = m.elementid
)
SELECT * INTO [#tmpA] FROM menu
我相信可以使用temp表来复制表,然后以某种方式使用插入到我的标准表中的标识,从elementid和根父级开始…但是,在那之后,我几乎不知道如何递归地udpate所有parentid,elementid与它们的关系…(在SQL Server中可能吗?)。
我看到如下情况:
创建过程[dbo]。[sp\u ta\u copy\u tutorial\u by \u id]
@开斋节
作为
不计数
声明@recid bigint
SET @recid = (SELECT IDENT_CURRENT('ta_tutorial_elements'));
WITH menu AS
(
SELECT parentid, elementid, elementtitle, sortorder, userid, createddate FROM dbo.ta_tutorial_elements WHERE (elementid = @eid)
UNION ALL
SELECT e.parentid, e.elementid, e.elementtitle, e.sortorder, e.userid, e.createddate FROM dbo.ta_tutorial_elements AS e INNER JOIN menu AS m ON e.parentid = m.elementid
)
SELECT * INTO [#tmpA] FROM menu
ALTER TABLE [#tmpA]
DROP COLUMN elementid
SELECT * INTO [#tmpB] FROM [#tmpA];
UPDATE b SET b.parentid =
CASE
WHEN b.parentid <> 0
THEN @recid
ELSE 0
END
FROM [#tmpB] as b
INSERT INTO [ta_tutorial_elements] SELECT * FROM [#tmpB]
DROP TABLE [#tmpA]
DROP TABLE [#tmpB]