假设类别树的深度不超过3级,这应该有效:
declare @Catergory table (
ID int not null,
Name nvarchar(10) not null,
ParentID int null
)
declare @Customer table (
ID int not null,
Name nvarchar(10) not null,
SurName nvarchar(10) not null,
Address nvarchar(30) not null,
CategoryId int not null
)
insert into @Catergory (ID, Name, ParentID)
values (1, 'A', null), (2, 'B', null),
(3, 'C', null), (4, 'AA', 1),
(5, 'CC', 3), (6, 'AAA', 4),
(7, 'BB', 2), (8, 'AAA', 4),
(9, 'CCC', 5), (10, 'AA', 1)
insert into @Customer (ID, Name, SurName, Address, CategoryId)
values (1, 'Duck', 'Duffy', '10 Steet', 10),
(2, 'Ben', 'Ten', '10 Steet', 6),
(3, 'Cat', 'Dog', '10 Steet', 5),
(4, 'Chicken', 'Wings', '10 Steet', 1),
(5, 'Fish', 'Water', '10 Steet', 7)
select *
from @Customer cust
join (
select ID, Name as CategoryName, null As CategoryType, null as SubCategory from @Catergory roots where ParentID is null
union
select mids.ID, roots.Name, mids.Name, null from @Catergory mids
join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
union
select leafs.ID, roots.Name, mids.Name, leafs.Name from @Catergory leafs
join @Catergory mids on leafs.ParentID = mids.ID
join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
) as struct on cust.CategoryId = struct.ID
order by cust.id
输出:
+
| ID | Name | SurName | Address | CategoryId | ID | CategoryName | CategoryType | SubCategory |
+
| 1 | Duck | Duffy | 10 Steet | 10 | 10 | A | AA | NULL |
| 2 | Ben | Ten | 10 Steet | 6 | 6 | A | AA | AAA |
| 3 | Cat | Dog | 10 Steet | 5 | 5 | C | CC | NULL |
| 4 | Chicken | Wings | 10 Steet | 1 | 1 | A | NULL | NULL |
| 5 | Fish | Water | 10 Steet | 7 | 7 | B | BB | NULL |
+
一些额外的列仍然存在,但我相信你可以将它们清除掉。请注意,其中一些类别列具有null值。这是因为如果客户处于高级别或中级别类别中,则没有合理的方法来填写这些列。
hierarchyid
? 层级数量固定吗? 如果是这样,您无需使用递归或CTE,只需进行3个自连接即可。 代码将比CTE更简单,性能更快。 - Panagiotis Kanavos