sql server显示视图以显示父级和子级产品

z9gpfhce 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(351)

我有一张这样的table：

+----+----------+----------+-----------+
| 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 |
+----+---------+---------+----------+

有人知道怎么做吗？

sql Join sql-server Hierarchy

来源：https://stackoverflow.com/questions/61910818/sql-server-to-display-a-view-to-show-products-at-parent-and-child-level

1条答案

按热度按时间

4sup72z81#

您可以使用两个级别的 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);

赞(0）回复(0）举报 2021-08-09

我来回答

sql server显示视图以显示父级和子级产品

我有一张这样的table：

1条答案

相关问题

热门标签

最新问答