我有这样的表结构：ID Name ParentId-------------------------1 Root 02 Business 13 Finance 24 Stock 35 Any 36 One 07 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 | | | | |
2 回答
绝地无双
TA贡献1946条经验 获得超4个赞
假设您的表的名称为“htable”，并且只要有多达四 (4) 个层次结构，就可以使用自连接来完成：
SELECT level1.ID, level1.Name, level2.ID, level2.Name, level3.ID, level3.Name, level4.ID, level4.NameFROM 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;
牛魔王的故事
TA贡献1830条经验 获得超3个赞
SELECT level1.ID, level1.Name, level2.ID, level2.Name, level3.ID, level3.Name, level4.ID, level4.NameFROM 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;
该查询返回不必要的 NULL，我只需要打印红色方形行。
- 2 回答
- 0 关注
- 91 浏览
添加回答
举报
0/150
提交
取消