如何通过父子关系检索数据 [英] How to Retrieve Data By Parent and Child Relation
本文介绍了如何通过父子关系检索数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE TABLE t1(id int ,parentid,name String( 20 ))
INSERT INTO t1 VALUES ( 1 , NULL ,' Root' )
INSERT INTO t1 VALUES ( 2 , 1 ,' < span class =code-string> Branch1')
INSERT INTO t1 VALUES ( 3 , 1 ,< span class =code-string >' 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 SubBranch2
how 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.ID
So what you really have to do, is revising your requirement and chosen solution for the data layout...
这篇关于如何通过父子关系检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文