父子sql表在php中显示为表

sg2wtvxw  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(319)

我有这样的表结构:

ID    Name     ParentId
-------------------------
1     Root      0
2     Business  1
3     Finance   2
4     Stock     3
5     Any       3
6     One       0
7     Two       6

有没有办法把所有的关系都放在一行呢?
这样地:

+------------------------------------------------------------------------------------+
|ID        | NAME       |    ID     | NAME      | ID       | NAME    | ID     | NAME |
|----------|------------|-----------|-----------|----------|---------|--------|------|
| 1        | Root       | 2         | Business  | 3        | Finance | 4      | Stock|
| 1        | Root       | 2         | Business  | 3        | Finance | 4      | Any  |     
| 1        | One        | 6         | Two       |          |         |        |      |
rm5edbpk

rm5edbpk1#

假设您的表有一个名称“htable”,并且只要有多达四(4)个层次结构级别,就可以使用self-join这样做:

SELECT  level1.ID, level1.Name,
        level2.ID, level2.Name,
        level3.ID, level3.Name,
        level4.ID, level4.Name
FROM htable AS level1
LEFT JOIN htable AS level2 ON level1.ID = level2.ParentId
LEFT JOIN htable AS level3 ON level2.ID = level3.ParentId
LEFT JOIN htable AS level4 ON level3.ID = level4.ParentId;
gojuced7

gojuced72#

SELECT  level1.ID, level1.Name,
        level2.ID, level2.Name,
        level3.ID, level3.Name,
        level4.ID, level4.Name
FROM htable AS level1
LEFT JOIN htable AS level2 ON level1.ID = level2.ParentId
LEFT JOIN htable AS level3 ON level2.ID = level3.ParentId
LEFT JOIN htable AS level4 ON level3.ID = level4.ParentId;

这个查询返回不必要的空值,我只需要打印红色的平方行。
下图。
返回的值

相关问题