当父子存储在同一个表中时显示父子关系 [英] Display Parent-Child relationship when Parent and Child are stored in same table

问题描述

我有如下的 SQL Server 表结构:

I have SQL Server table structure like below:

ID    Name     ParentID
-----------------------
1     Root       NULL
2     Business   1
3     Finance    1
4     Stock      3

我想在我的网页中显示详细信息

I want to display the details in my web page like

ID    Name     ParentName
-------------------------
1     Root      -
2     Business  Root
3     Finance   Root
4     Stock     Finance    

如何构建我的 SQL 查询?

How can I construct my SQL query?

推荐答案

试试这个...

SELECT a.ID, a.Name, b.Name AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID

使用左连接，查询将找不到任何要连接的 NULL 列，并为 ParentName 列返回空白.

With the left join, the query will not find anything to join for the NULL and return blank for the ParentName column.

如果您不希望Parent"列为空，但希望显示-"破折号，请使用此查询.

If you do not want the 'Parent' column to be blank, but want to show a '-' dash then use this query.

SELECT a.ID, a.Name, COALESCE(b.Name,'-') AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID

这篇关于当父子存储在同一个表中时显示父子关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！