SQL 当父子存储在同一个表中时显示父子关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4116416/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display Parent-Child relationship when Parent and Child are stored in same table
提问by kirikamal
I have SQL Server table structure like below:
我有如下 SQL Server 表结构：
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
How can I construct my SQL query?
如何构建我的 SQL 查询？
回答by Eric
try this...
尝试这个...
SELECT a.ID, a.Name, b.Name AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID
With the left join, the query will not find anything to join for the NULL and return blank for the ParentNamecolumn.
使用左连接，查询将找不到任何要连接 NULL 的内容，并为该ParentName列返回空白。
EDIT:
编辑：
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
回答by OMG Ponies
Assuming SQL Server 2005+, use a recursive CTE like this:
假设 SQL Server 2005+，使用这样的递归 CTE：
WITH hierarchy AS (
SELECT t.id,
t.name,
t.parentid,
CAST(NULL AS VARCHAR(50)) AS parentname
FROM YOUR_TABLE t
WHERE t.parentid IS NULL
UNION ALL
SELECT x.id,
x.name,
x.parentid,
y.name
FROM YOUR_TABLE x
JOIN hierarchy y ON y.id = x.parentid)
SELECT s.id,
s.name,
s.parentname
FROM hierarchy s
The CASTing of the NULL might look odd, but SQL Server defaults the data type to INT unless specified in a manner like you see in my query.
NULL 的 CASTing 可能看起来很奇怪，但 SQL Server 将数据类型默认为 INT，除非以您在我的查询中看到的方式指定。
回答by ryanlahue
SELECT CH.ID, CH.NAME, ISNULL(PA.NAME, '-') AS "PARENTNAME"
FROM TBL CH
LEFT OUTER JOIN TBL PA
ON CH.PARENTID = PA.ID
回答by Meet Prajapati
I am facing same situation. I want to fetch all child list of particular parent from same table, where as MySQL is not provided Recursive CTEin below MySQL 8.
我面临同样的情况。我想从同一个表中获取特定父级的所有子列表，因为 MySQL 没有在 MySQL 8 以下提供递归 CTE。
I had resolved my issue with recursive store procedure. In that we need to set max_sp_recursion_depthto recursive store procedure call.
我已经用递归存储过程解决了我的问题。我们需要将max_sp_recursion_depth设置为递归存储过程调用。
My table structure is below
我的表结构如下
My store procedure(With recursion) is below:
我的存储过程（带递归）如下：
DROP PROCEDURE IF EXISTS getAllChilds;
DELIMITER $$
SET @@SESSION.max_sp_recursion_depth=25; $$
CREATE PROCEDURE getAllChilds (IN inId int(11), INOUT list_obj text, IN end_arr CHAR(1))
BEGIN
DECLARE stop_cur INTEGER DEFAULT 0;
DECLARE _id INTEGER DEFAULT 0;
DECLARE _name VARCHAR(20) DEFAULT 0;
DEClARE curSelfId CURSOR FOR SELECT id, name FROM new_table where parentId = inId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop_cur = 1;
OPEN curSelfId;
getSelfId: LOOP
FETCH curSelfId INTO _id, _name;
IF stop_cur = 1 THEN LEAVE getSelfId; END IF;
IF list_obj is null or list_obj = "" then
SET list_obj = CONCAT("[","{",'"name":"',_name,'","id":"',_id,'"',"}");
else
SET list_obj = CONCAT(list_obj,',', "{",'"name":"',_name,'","id":"',_id,'"',"}");
end if;
CALL getAllChilds(_id,list_obj,"");
END LOOP getSelfId;
CLOSE curSelfId;
IF end_arr is not null and end_arr != "" then
SET list_obj = CONCAT(list_obj,end_arr);
SELECT @ids;
end if;
END$$
DELIMITER ;
To Call this stored procedure we need to pass 3 arguments,
要调用这个存储过程，我们需要传递 3 个参数，
- Parent id
- Empty/null object
End array sign.(for append only last object instead of all objects).
CALL getAllChilds(1,@ids,']');
- 家长编号
- 空/空对象
结束数组符号。（仅追加最后一个对象而不是所有对象）。
CALL getAllChilds(1,@ids,']');
Using this store procedure you may get all level child in JSON string. You can parse this json string and convert in any OBJECT using any JSONparser.
使用此存储过程，您可以获得 JSON 字符串中的所有级别子项。您可以解析此 json 字符串并使用任何 JSONparser 在任何 OBJECT 中进行转换。
Or
或者
we can wrap this answerin store procedure for use it into any programming language like JAVA. we are wrapping this into store procedure because we can't use :=in query. Here we are using find_in_setfunction. My store procedure(Without recursion).
我们可以将这个答案包装在存储过程中，以便在任何编程语言（如 JAVA）中使用它。我们将其包装到存储过程中，因为我们不能在查询中使用:=。这里我们使用find_in_set函数。我的存储过程（没有递归）。
DROP PROCEDURE IF EXISTS getAllChilds;
DELIMITER $$
CREATE PROCEDURE getAllChilds (IN inId int(11))
BEGIN
select id,
name,
parentid
from
(select * from new_table
order by parentid, id) new_table,
(select @pv := inId) initialisation
where
find_in_set(parentid, @pv) > 0
and @pv := concat(@pv, ',', id);
END$$
DELIMITER ;
To call this store procedure we need just parent Id.
要调用此存储过程，我们只需要父 ID。
CALL getAllChilds(1);
回答by Prasanna Iyer
Like someone posted earlier, it needs to be a recursive function. There can be multiple scenarios - One Parent to Multiple Child (Level 1 Hierarchy) Parent 1 has Child 1 Parent 1 has Child 2 Child to Multiple Child (Level 2 Hierarchy) And so on.
就像之前发布的某人一样，它需要是一个递归函数。可以有多种场景 - 一个父级到多个子级（级别 1 层次结构）父级 1 有子级 1 父级 1 有子级 2 子级到多个子级（级别 2 层次结构）等等。
My example has parent curve id and child curve id. For example,
我的示例具有父曲线 ID 和子曲线 ID。例如，
WITH hierarchy AS
(select UT.parent_curve_id as origin, UT.*
from myTable UT
WHERE UT.parent_curve_id IN ( 1027455, 555)
UNION ALL
select h.origin, UT.*
from myTable UT
JOIN hierarchy h ON h.child_curve_id = UT.parent_curve_id
)
SELECT *
from hierarchy
order by unique_key
回答by kuriouscoder
I think the following query would work. I've not tested it.
我认为以下查询会起作用。我没有测试过。
SELECT
ID
, name
, (CASE WHEN parent_name IS NULL THEN '-' ELSE parent_name END)
FROM
RELATIONS
, (SELECT
parentID
, name AS parent_name
FROM
RELATION) PARENT
WHERE
RELATIONS.parentId = PARENT.parentId
Basically, what I'm doing is doing is choosing parent information, and relating it to each tuple.
基本上，我正在做的是选择父信息，并将其与每个元组相关联。
回答by Navid Farhadi
This is a simple way:
这是一个简单的方法：
SELECT ID,Name,(SELECT TOP 1 Name FROM Table t WHERE t.ParentID=ParentID) AS ParentName FROM Table
