本文介绍了如何通过父子关系检索数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE TABLE t1(id int ,parentid,name String( 20 )) INSERT INTO t1 VALUES ( 1 , NULL ,' Root' ) INSERT INTO t1 VALUES ( 2 , 1 ,' Branch1') INSERT INTO t1 VALUES ( 3 , 1 ,' Branch2') INSERT INTO t1 VALUES ( 4 , 3 ,' SubBranch1') INSERT INTO t1 VALUES ( 5 , 2 ,' SubBranch2')
i希望显示表格为
root Branch1 SubBranch1 root Branch2 SubBranch2我怎样才能得到这个
解决方案它可能让你感到惊讶,但SQL只能处理表格数据...你想要的输出取决于你的树深入 - 因为树中的任何新级别都应该在输出中创建一个新列... 可以创建一个动态查询,其中每个级别都有一个左连接到原始表格,但输出也是动态的... SELECT * FROM T1 LEFT JOIN T1 AS T2 ON T2.PARENTID = T1.ID LEFT JOIN T1 AS T3 ON T3.PARENTID = T2.ID
所以你真正要做的就是修改你的要求和选择的数据布局解决方案...
CREATE TABLE t1 ( id int , parentid, name String(20) ) INSERT INTO t1 VALUES ( 1, NULL, 'Root' ) INSERT INTO t1 VALUES ( 2, 1, 'Branch1' ) INSERT INTO t1 VALUES ( 3, 1, 'Branch2' ) INSERT INTO t1 VALUES ( 4, 3, 'SubBranch1' ) INSERT INTO t1 VALUES ( 5, 2, 'SubBranch2' )i want to display table as
root Branch1 SubBranch1 root Branch2 SubBranch2how can i get this one
解决方案 It may surprise you, but SQL can only handle tabular data...Your desired output depends on the how deep the tree goes - as any new level in the tree should create a new column in the output... It is possible to create a dynamic query where for every level there is a left join to the original table, but the output will be dynamic too... SELECT * FROM T1 LEFT JOIN T1 AS T2 ON T2.PARENTID = T1.ID LEFT JOIN T1 AS T3 ON T3.PARENTID = T2.IDSo what you really have to do, is revising your requirement and chosen solution for the data layout...
更多推荐
如何通过父子关系检索数据
发布评论