我是SQL新手，正在尝试对表中的所有行使用CTE，以使用分层信息更新每行中的多个列。 我在网上读了很多例子，但没有一个能准确描述我的需求，所以我希望有人能帮助我。
我有一个研究样品表存储在货架>机架>盒子的层次结构中，其中货架是最高级别，盒子是最低的，样品在盒子下面。搁板、机架和箱子也作为单独的行在此表中。 所有项目都有一个名为"name"的字段来标识它们。 它们都通过 ID 和parent_ID连接。 有点像管理结构。
我需要填充样本每行中的所有级别值。 它比我在网上找到的经理-员工示例更复杂，它需要存储 CTE 返回的值，而不是简单地执行 SELECT 来显示它们，正如我找到的示例所示。
为了在所有行上运行 CTE，我尝试将整个 CTE 嵌套在 WHILE 循环中，但它只是循环单个值而不是所有行。
下面是我目前正在处理的代码，用于返回一个示例值的层次结构;"tempspec"是示例表：
DECLARE @TID float;
SET @TID = 39059;
WITH cte AS
(
SELECT ID,
Parent_Id,
Name,
Study_ID,
Loc_Box,
Loc_Shelf,
Loc_Rack,
Loc_Type
FROM tempspec
WHERE ID = @TID and Study_ID = 'XXX'
UNION all
SELECT tempspec.Id,
tempspec.Parent_Id,
tempspec.Name,
tempspec.Study_ID,
tempspec.Loc_Box,
tempspec.Loc_Shelf,
tempspec.Loc_Rack,
tempspec.Loc_Type
FROM tempspec
JOIN cte on tempspec.Id = cte.Parent_Id
)
SELECT E1.name, E1.ID, E1.Parent_ID,E1.Loc_Type,ISNULL(E2.name,'TOP')
FROM cte E1
LEFT JOIN cte E2
ON E1.parent_id = E2.ID;
但是，如果我尝试用 UPDATE 替换 SELECT，它会运行并说表中的所有行都已更新，但尚未存储任何内容：
UPDATE E1
SET E1.Loc_Box = E2.Loc_Box,
E1.Loc_Rack = E2.Loc_Rack,
E1.Loc_Shelf = E2.Loc_Shelf
FROM tempspec E1
LEFT JOIN cte E2
ON E1.parent_id = E2.ID;
如果我尝试将 CTE 嵌套在这个 WHILE 循环中("P"表示示例行，所以我只遍历它们的树(，我无法将 ID 分配给@TID，或者当我尝试以其他方式永久循环时：
WHILE EXISTS(SELECT * FROM tempspec WHERE Loc_Type = 'P')
BEGIN
SET @TID = ID;
我做错了什么？ 我尝试了各种格式，但似乎没有任何效果。 提前感谢您的帮助！
以下是请求的测试数据。 我想用样本父行中的名称值填充货架、机架和盒子(type='P'(：
DROP TABLE dbo.TEST_DATA;
CREATE TABLE dbo.TEST_DATA (int ID, int Parent_Id,varchar(30) Name, varchar(10) Type,varchar(30) Shelf,varchar(30) Rack,varchar(30) Box)
INSERT INTO TEST_DATA (39702, 1664, 0228MBDNAERA1, 'P','','','');
INSERT INTO TEST_DATA (39703, 1664, 0230MBDNAERA1, 'P','','','');
INSERT INTO TEST_DATA (39704, 1664, 0231MBDNAERA1, 'P','','','');
INSERT INTO TEST_DATA (39726, 1744, 0228MBDNAERA2, 'P','','','');
INSERT INTO TEST_DATA (39727, 1744, 0230MBDNAERA2, 'P','','','');
INSERT INTO TEST_DATA (39728, 1744, 0231MBDNAERA2, 'P','','','');
INSERT INTO TEST_DATA (39764, 1752, 0228MBDNAERA3, 'P','','','');
INSERT INTO TEST_DATA (39766, 1752, 0230MBDNAERA3, 'P','','','');
INSERT INTO TEST_DATA (39768, 1752, 0231MBDNAERA3, 'P','','','');
INSERT INTO TEST_DATA (1744, 1652, MBDNAERA2 - 3, 'B','','','');
INSERT INTO TEST_DATA (1752, 1732, MBDNAERA3 - 3, 'B','','','');
INSERT INTO TEST_DATA (1664, 1652, MBDNAERA1 - 3, 'B','','','');
INSERT INTO TEST_DATA (1732, 1617, Rack R, 'R','','','');
INSERT INTO TEST_DATA (1652, 1617, Rack Q, 'R','','','');
INSERT INTO TEST_DATA (1617, 2, Shelf 4, 'S','','','');
INSERT INTO TEST_DATA (2, NULL, Freezer, 'F','','','');
一个样本的示例数据层次结构和我试图获取的结果行(对格式错误感到抱歉(：
ID Parent_Id Name Type Shelf Rack Box
39702 1664 0228MBDNAERA1 'P'
1664 1652 MBDNAERA1 - 3 'B'
1652 1617 Rack Q 'R'
1617 2 Shelf 4 'S'
2 NULL Freezer 'F'
ID Parent_Id Name Type Shelf Rack Box
39702 1664 0228MBDNAERA1 'P' Shelf 4 Rack Q MBDNAERA1 - 3
试一试：
DECLARE @TEST_DATA as TABLE (ID int , Parent_Id int ,Name varchar(30) , Type varchar(10) )
INSERT @TEST_DATA VALUES (39702, 1664, '0228MBDNAERA1', 'P');
INSERT @TEST_DATA VALUES (39703, 1664, '0230MBDNAERA1', 'P');
INSERT @TEST_DATA VALUES (39704, 1664, '0231MBDNAERA1', 'P');
INSERT @TEST_DATA VALUES (39726, 1744, '0228MBDNAERA2', 'P');
INSERT @TEST_DATA VALUES (39727, 1744, '0230MBDNAERA2', 'P');
INSERT @TEST_DATA VALUES (39728, 1744, '0231MBDNAERA2', 'P');
INSERT @TEST_DATA VALUES (39764, 1752, '0228MBDNAERA3', 'P');
INSERT @TEST_DATA VALUES (39766, 1752, '0230MBDNAERA3', 'P');
INSERT @TEST_DATA VALUES (39768, 1752, '0231MBDNAERA3', 'P');
INSERT @TEST_DATA VALUES (1744, 1652, 'MBDNAERA2 - 3', 'B');
INSERT @TEST_DATA VALUES (1752, 1732, 'MBDNAERA3 - 3', 'B');
INSERT @TEST_DATA VALUES (1664, 1652, 'MBDNAERA1 - 3', 'B');
INSERT @TEST_DATA VALUES (1732, 1617, 'Rack R', 'R');
INSERT @TEST_DATA VALUES (1652, 1617, 'Rack Q', 'R');
INSERT @TEST_DATA VALUES (1617, 2, 'Shelf 4', 'S');
INSERT @TEST_DATA VALUES (2, NULL, 'Freezer', 'F');
-- SELECT
;with
cte_product as (
Select * from @TEST_DATA where Type = 'P'),
cte_box as (
Select * from @TEST_DATA where Type = 'B'),
cte_shelf as (
Select * from @TEST_DATA where Type = 'S'),
cte_rack as (
Select * from @TEST_DATA where Type = 'R')
Select * from
cte_product p
left join cte_box b
on
p.Parent_Id = b.ID
inner join cte_rack r
on
b.Parent_Id = r.ID
inner join cte_shelf s
on
r.Parent_Id = s.ID
结果
ID Parent_Id Name Type ID Parent_Id Name Type ID Parent_Id Name Type ID Parent_Id Name Type
----------- ----------- ------------------------------ ---------- ----------- ----------- ------------------------------ ---------- ----------- ----------- ------------------------------ ---------- ----------- ----------- ------------------------------ ----------
39726 1744 0228MBDNAERA2 P 1744 1652 MBDNAERA2 - 3 B 1652 1617 Rack Q R 1617 2 Shelf 4 S
39727 1744 0230MBDNAERA2 P 1744 1652 MBDNAERA2 - 3 B 1652 1617 Rack Q R 1617 2 Shelf 4 S
39728 1744 0231MBDNAERA2 P 1744 1652 MBDNAERA2 - 3 B 1652 1617 Rack Q R 1617 2 Shelf 4 S
39764 1752 0228MBDNAERA3 P 1752 1732 MBDNAERA3 - 3 B 1732 1617 Rack R R 1617 2 Shelf 4 S
39766 1752 0230MBDNAERA3 P 1752 1732 MBDNAERA3 - 3 B 1732 1617 Rack R R 1617 2 Shelf 4 S
39768 1752 0231MBDNAERA3 P 1752 1732 MBDNAERA3 - 3 B 1732 1617 Rack R R 1617 2 Shelf 4 S
39702 1664 0228MBDNAERA1 P 1664 1652 MBDNAERA1 - 3 B 1652 1617 Rack Q R 1617 2 Shelf 4 S
39703 1664 0230MBDNAERA1 P 1664 1652 MBDNAERA1 - 3 B 1652 1617 Rack Q R 1617 2 Shelf 4 S
39704 1664 0231MBDNAERA1 P 1664 1652 MBDNAERA1 - 3 B 1652 1617 Rack Q R 1617 2 Shelf 4 S
--UPDATE TABLE
;with
cte_product as (
Select * from @TEST_DATA where Type = 'P'),
cte_box as (
Select * from @TEST_DATA where Type = 'B'),
cte_shelf as (
Select * from @TEST_DATA where Type = 'S'),
cte_rack as (
Select * from @TEST_DATA where Type = 'R')
UPDATE D
SET
Shelf = S.name
,Rack = r.Name
,Box = b.Name
FROM
@TEST_DATA d
Inner join
cte_product p
left join cte_box b
on
p.Parent_Id = b.ID
inner join cte_rack r
on
b.Parent_Id = r.ID
inner join cte_shelf s
on
r.Parent_Id = s.ID
On
d.ID = p.ID
select * from @TEST_DATA
结果
ID Parent_Id Name Type Shelf Rack Box
----------- ----------- ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
39702 1664 0228MBDNAERA1 P Shelf 4 Rack Q MBDNAERA1 - 3
39703 1664 0230MBDNAERA1 P Shelf 4 Rack Q MBDNAERA1 - 3
39704 1664 0231MBDNAERA1 P Shelf 4 Rack Q MBDNAERA1 - 3
39726 1744 0228MBDNAERA2 P Shelf 4 Rack Q MBDNAERA2 - 3
39727 1744 0230MBDNAERA2 P Shelf 4 Rack Q MBDNAERA2 - 3
39728 1744 0231MBDNAERA2 P Shelf 4 Rack Q MBDNAERA2 - 3
39764 1752 0228MBDNAERA3 P Shelf 4 Rack R MBDNAERA3 - 3
39766 1752 0230MBDNAERA3 P Shelf 4 Rack R MBDNAERA3 - 3
39768 1752 0231MBDNAERA3 P Shelf 4 Rack R MBDNAERA3 - 3
1744 1652 MBDNAERA2 - 3 B
1752 1732 MBDNAERA3 - 3 B
1664 1652 MBDNAERA1 - 3 B
1732 1617 Rack R R
1652 1617 Rack Q R
1617 2 Shelf 4 S
2 NULL Freezer F