SQL Server 显示一个视图，以在父级和子级显示产品

Question

我有这样的表格：

+----+----------+----------+-----------+
| ID |   Name   | Is_Group | Parent_id |
+----+----------+----------+-----------+  
|  1 | Clothes  |        1 | Null      |  
|  2 | Food     |        1 | Null      |  
|  3 | fastfood |        1 | 2         |  
|  4 | T-shirt  |        0 | 1         |
|  5 | skirt    |        0 | 1         |
|  6 | pizza    |        0 | 3         |
|  7 | snack    |        0 | 3         |
+----+----------+----------+-----------+

我希望有一个水平表示用于报告，例如：

+----+---------+---------+----------+
| ID |  Name   | level1  |  level2  |
+----+---------+---------+----------+
|  4 | T-shirt | Clothes | Null     |
|  5 | skirt   | Clothes | Null     |
|  6 | pizza   | Food    | fastfood |
|  7 | snack   | Food    | fastfood |
+----+---------+---------+----------+

有人知道怎么做吗？

Answer 1

您可以使用两个级别的left join：

select t.*,
coalesce(tpp.name, tp.name) as level1,
(case when tpp.name is not null then tp.name end) as level2
from t left join
t tp
on t.parent_id = tp.id left join
t tpp
on tp.parent_id = tpp.parent_id
where not exists (select 1
from t tc
where tc.parent_id = t.id);