表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怎么写,才能过滤掉没有子节点的数据啊.

解决方案 »

  1.   

    create table test(TreeList varchar(200))
    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
    )
      

  2.   

    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"(所影响的行数为 6 行)
      

  3.   

    CREATE TABLE #TREEVIEW(TreeList VARCHAR(50))
    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"
      

  4.   

    declare @TreeView table(TreeList varchar(50))
    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"
    */
      

  5.   

    --个人觉得这种编号方法不好,因为查询并不方便
    --可将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)+'_')
    )
    )
      

  6.   

    --测试数据
    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'