表TreeView中有一个TreeList的字段有如下数据:
TreeList
==================================
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"tree.nodes["0_200"] = "dsadsa"tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"怎么通过SQL语句来过滤掉tree.nodes["0_200"] = "dsadsa"的数据,查询出:
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"=============================================
过滤掉的原因:tree.nodes["0_200"] = "dsadsa"节点下没有子节点.这个SQL怎么写,才能过滤掉没有子节点的数据啊.
TreeList
==================================
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"tree.nodes["0_200"] = "dsadsa"tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"怎么通过SQL语句来过滤掉tree.nodes["0_200"] = "dsadsa"的数据,查询出:
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"=============================================
过滤掉的原因:tree.nodes["0_200"] = "dsadsa"节点下没有子节点.这个SQL怎么写,才能过滤掉没有子节点的数据啊.
insert test select 'tree.nodes["0_100"] = "ddd"'
union all select 'tree.nodes["100_10001"] = "34543"'
union all select 'tree.nodes["100_10002"] = "yyyy"'
union all select 'tree.nodes["0_200"] = "dsadsa""'
union all select 'tree.nodes["0_300"] = "dsadfdsfdssa"'
union all select 'tree.nodes["300_30001"] = "ddfs"'
union all select 'tree.nodes["300_30002"] = "fdsfds"'select * from test where substring(TreeList,charindex('_',TreeList),4) in
(
select substring(TreeList,charindex('_',TreeList),4) from test
group by substring(TreeList,charindex('_',TreeList),4)
having count(*)>1
)
--------------------------------
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"
tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"(所影响的行数为 6 行)
INSERT #TREEVIEW SELECT 'tree.nodes["0_100"] = "ddd"'
INSERT #TREEVIEW SELECT 'tree.nodes["100_10001"] = "34543"'
INSERT #TREEVIEW SELECT 'tree.nodes["100_10002"] = "yyyy"'
INSERT #TREEVIEW SELECT 'tree.nodes["0_200"] = "dsadsa"'
INSERT #TREEVIEW SELECT 'tree.nodes["0_300"] = "dsadfdsfdssa"'
INSERT #TREEVIEW SELECT 'tree.nodes["300_30001"] = "ddfs"'
INSERT #TREEVIEW SELECT 'tree.nodes["300_30002"] = "fdsfds"'
------------------------------------------------------------
SELECT * FROM #TREEVIEW A
WHERE NOT(CHARINDEX('"0_',TreeList)>0 AND NOT EXISTS
(SELECT 0 FROM #TREEVIEW B
WHERE CHARINDEX('["'+SUBSTRING(A.TreeList,15,CHARINDEX('"]',
A.TreeList)-15)+'_',B.TreeList)>0))
------------------------------------------------------------
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"
tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"
insert @TreeView
select 'tree.nodes["0_100"] = "ddd"' union all
select 'tree.nodes["100_10001"] = "34543"' union all
select 'tree.nodes["100_10002"] = "yyyy"' union all
select 'tree.nodes["0_200"] = "dsadsa"' union all
select 'tree.nodes["0_300"] = "dsadfdsfdssa"' union all
select 'tree.nodes["300_30001"] = "ddfs"' union all
select 'tree.nodes["300_30002"] = "fdsfds"'
SELECT * FROM @TreeView AS a WHERE exists
(select 1 from @TreeView
where charindex(replace(substring(a.TreeList,1,charindex('"]',a.TreeList) - 1) +
case when charindex('"0_',a.TreeList) > 0 then '_' else '' end ,'"0_','"') ,TreeList) > 0)/*结果
TreeList
--------------------------------------------
tree.nodes["0_100"] = "ddd"
tree.nodes["100_10001"] = "34543"
tree.nodes["100_10002"] = "yyyy"
tree.nodes["0_300"] = "dsadfdsfdssa"
tree.nodes["300_30001"] = "ddfs"
tree.nodes["300_30002"] = "fdsfds"
*/
--可将0_100以"_"分开,存两个字段会好些。
--我理解楼主的意思是过滤掉没有子节点的根节点select * from test a where left(treelist,2)!='0_' or( left(treelist,2)='0_' and exists(
select * from test where left(treelist,4)=(right(a.treelist,3)+'_')
)
)
create table test(TreeList varchar(20),value varchar(20))
insert test select '0_100','ddd'
union all select '100_10001','34543'
union all select '100_10002','yyyy'
union all select '0_200','dsadsa'
union all select '0_300','dsadfdsfdssa'
union all select '300_30001','ddfs'
union all select '300_30002','fdsfds'