各位大哥谢谢你们,可能是我吧问题没说清楚,我看好多热心的大哥的SQl语句中直接把"一级","二级"都做了条件,这样写好像不满足我现在的要求.我再把数据给大家说一下,呵呵,还是希望能得到各位热心帮助:呵呵,就像楼上一位大哥说的那样,我做测试的东西就是一个树形结构,在这个树形结构中最多有三级节点,
第一级节点没有父节点,那么他的parent的值就是null,其他的二三级节点就一次类推;(其中有些节点只有二层,就是说没有三级节点])其实表中用到的字段就2个,一个是name ,一个是parent,且name和parent中数据是有关联关系的,name | parent
-------------------------
parm null
commonParm parm
coinParm commonParm
rateParm commonParm
bankParm commonParm
report null
singleFundReport report
manyFundReport report
thing singleFundRepor
attemper singleFundRepor
balanceTab manyFundReport
balanceChange manyFundReport
trade null
businessbalance trade
workDateparm trade
--------------------------------
那个根据上面的数据查询出来的结果就是
列一 | 列二 | 列三
---------------------------------
para commonParm coinParm
para commonParm rateParm
para commonParm bankParm
report singleFundReport thing
report singleFundReport attemper
report manyFundReport balanceTab
report manyFundReport balanceChange
trade businessbalance
trade workDateparm
第一级节点没有父节点,那么他的parent的值就是null,其他的二三级节点就一次类推;(其中有些节点只有二层,就是说没有三级节点])其实表中用到的字段就2个,一个是name ,一个是parent,且name和parent中数据是有关联关系的,name | parent
-------------------------
parm null
commonParm parm
coinParm commonParm
rateParm commonParm
bankParm commonParm
report null
singleFundReport report
manyFundReport report
thing singleFundRepor
attemper singleFundRepor
balanceTab manyFundReport
balanceChange manyFundReport
trade null
businessbalance trade
workDateparm trade
--------------------------------
那个根据上面的数据查询出来的结果就是
列一 | 列二 | 列三
---------------------------------
para commonParm coinParm
para commonParm rateParm
para commonParm bankParm
report singleFundReport thing
report singleFundReport attemper
report manyFundReport balanceTab
report manyFundReport balanceChange
trade businessbalance
trade workDateparm
create table #tb
(
name varchar(50),
parent varchar(50)
)
insert into #tb
select 'parm',null union all
select 'commonParm','parm' union all
select 'coinParm','commonParm' union all
select 'rateParm','commonParm' union all
select 'bankParm','commonParm' union all
select 'report',null union all
select 'singleFundReport','report' union all
select 'manyFundReport','report' union all
select 'thing','singleFundReport' union all
select 'attemper','singleFundReport' union all
select 'balanceTab','manyFundReport' union all
select 'balanceChange','manyFundReport' union all
select 'trade',null union all
select 'businessbalance','trade' union all
select 'workDateparm','trade' select
a.name,b.name,c.name
from
#tb a join
#tb b on a.name = b.parent and a.parent is null left join
#tb c on b.name = c.parentname name name
---------------- ---------------- ----------------
parm commonParm coinParm
parm commonParm rateParm
parm commonParm bankParm
report singleFundReport thing
report singleFundReport attemper
report manyFundReport balanceTab
report manyFundReport balanceChange
trade businessbalance NULL
trade workDateparm NULL(9 行受影响)