当父项和子项存储在同一张表中时，如何显示它们的父子关系

Question

当父项和子项存储在同一张表中时，如何显示它们的父子关系

15

我有一个类似下面的 SQL Server 表结构：

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

我想在我的网页中展示细节信息，例如：

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

我该如何构建我的SQL查询？

- kirikamal

父ID是否引用了同一张表中的ID列？ - Eric

@kirkamal，您能标记一个答案为已采纳吗？ - Eric

8个回答

12

假设使用 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

将NULL转换为INT类型可能看起来很奇怪，但如果没有像我查询中所见那样指定数据类型，SQL Server会默认使用INT类型。

- OMG Ponies

这必须是被接受的答案。此查询会自动下降到所需的任意级别，而无需任何额外的LEFT JOIN。 - Eugene

4

我面临相同的情况。我想从同一张表中获取特定父级的所有子列表，然而MySQL 8以下版本不提供递归CTE。

我通过递归存储过程解决了我的问题。在其中，我们需要将max_sp_recursion_depth设置为递归存储过程调用。

我的表结构如下：

我的带递归的存储过程如下：

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 ;

调用这个存储过程需要传递3个参数：

父级id
空/ null对象
结束数组标志（为追加只附加最后一个对象而非所有对象）。

CALL getAllChilds(1,@ids,']');

使用此存储过程，您可以获取JSON字符串中的所有子级别。您可以解析此JSON字符串并使用任何JSON解析器将其转换为任何对象。

或者

我们可以将此答案包装在存储过程中，以便在任何编程语言中使用它，如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 ;

调用此存储过程只需要父级 Id。

CALL getAllChilds(1);

- Meet Prajapati

3

SELECT CH.ID, CH.NAME, ISNULL(PA.NAME, '-') AS "PARENTNAME"
FROM TBL CH
LEFT OUTER JOIN TBL PA
ON CH.PARENTID = PA.ID

- ryanlahue

1

我认为以下查询语句应该可以工作。我还没有测试过。

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

基本上，我所做的是选择父级信息，并将其与每个元组相关联。

- kuriouscoder

1

就像之前有人发帖说的那样，它需要是一个递归函数。可能会有多种情况 - 一个父级对应多个子级（一级层次结构）父级1拥有子级1 父级1拥有子级2 子级对应多个子级（二级层次结构）等等。

我的例子有父曲线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

- Prasanna Iyer

0

这是一个简单的方法：

SELECT ID,Name,(SELECT TOP 1 Name FROM Table t WHERE t.ParentID=ParentID) AS ParentName FROM Table

- Navid Farhadi

0

对于Mysql，以防有人需要。

WITH RECURSIVE 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

- kesammio

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Eric · Accepted Answer

试试这个...

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 列将返回空值。

编辑：

如果你不想让 'Parent' 列为空，但是想显示一个 '-' 短横线，则使用此查询。

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