在树结构表中查找空节点的SQL查询

我正在开发一个Android应用程序，它将一些数据作为树状结构存储在SQLite表中。

作为示例，表的相关部分具有以下结构:

parentId33

根据@tinazmu的回答进行了一些研究后，我终于提出了一个查询，似乎在SQLite中工作得很好。

WITH AncestryTree AS (
SELECT id, parentId, type
FROM Tree
WHERE parentId IS NOT NULL
UNION ALL
SELECT T.id, AT.parentId, T.type
FROM AncestryTree AT
JOIN Tree T ON AT.id = T.parentId
)
SELECT id
FROM Tree
WHERE type = 'node'
AND id NOT IN (
SELECT DISTINCT parentId
FROM AncestryTree
WHERE type = 'data'
)

这个查询可以在db-fiddle中测试，但是如果有人指出这个查询的任何缺陷或优化，那就太好了。

我可以用递归查找数据节点在每个节点的节点。第一个是SqlServer:

with treeTable as (
select *
from (values 
(1,'1','node',0,null)
,(2,'2','node',1,1)
,(3,'3','node',1,1)
,(4,'4','node',2,2)
,(5,'5','node',2,3)
,(6,'6','node',2,3)
,(7,'7','node',3,4)
,(8,'8','node',3,6)
,(9,'**','data',2,2)
,(10,'**','data',2,2)
,(11,'**','data',3,4)
,(12,'**','data',3,5)
) T(id, name,   type,   depth,  parentId)
),
Hry as (
SELECT StartNode=TN.id, ThisNode=TN.id, TN.name, tn.type, tn.depth, L=0
FROM treeTable TN
union all
SELECT StartNode=H.StartNode, ThisNode=TN.id, TN.name, TN.type, TN.depth, L=L+1
FROM treeTable TN
inner join 
Hry H
on H.ThisNode=TN.ParentId
)
/* Now repeat for each 'node' node: recursively traverse each sub-tree and see if there are data nodes underneath */
select T.id
from treeTable T
left join
Hry H
on T.id=H.StartNode
and H.type='data'
where T.type='node'
and H.StartNode is null

Sqlite的

with treeTable as (
select  1 as id, '1'as name,'node' as type,0 as depth ,null as parentID union all
select  2 as id, '2'as name,'node' as type,1 as depth ,   1 as parentID union all
select  3 as id, '3'as name,'node' as type,1 as depth ,   1 as parentID union all
select  4 as id, '4'as name,'node' as type,2 as depth ,   2 as parentID union all
select  5 as id, '5'as name,'node' as type,2 as depth ,   3 as parentID union all
select  6 as id, '6'as name,'node' as type,2 as depth ,   3 as parentID union all
select  7 as id, '7'as name,'node' as type,3 as depth ,   4 as parentID union all
select  8 as id, '8'as name,'node' as type,3 as depth ,   6 as parentID union all
select  9 as id,'**'as name,'data' as type,2 as depth ,   2 as parentID union all
select 10 as id,'**'as name,'data' as type,2 as depth ,   2 as parentID union all
select 11 as id,'**'as name,'data' as type,3 as depth ,   4 as parentID union all
select 12 as id,'**'as name,'data' as type,3 as depth ,   5 as parentID 
),
Hry as (
SELECT TN.id as StartNode, TN.id as ThisNode, TN.name, tn.type, tn.depth, 0 as L
FROM treeTable TN
union all
SELECT H.StartNode as StartNode, TN.id as ThisNode, TN.name, TN.type, TN.depth, L+1 as L
FROM treeTable TN
inner join 
Hry H
on H.ThisNode=TN.ParentId
)
/* Now repeat for each 'node' node: recursively traverse each sub-tree and see if there are data nodes underneath */
select T.id
from treeTable T
left join
Hry H
on T.id=H.StartNode
and H.type='data'
where T.type='node'
and H.StartNode is null

相关内容

最新更新

热门标签：