SQL查询问题

[mytest114]

各位大哥谢谢你们,可能是我吧问题没说清楚,我看好多热心的大哥的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

[jstoic]

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.parent

name             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 行受影响)

[jaydom]

[Quote=引用 1 楼 jstoic 的回复:]
SQL code

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' uni……
[/Quote]
貌似结果已经出来了，帮顶

[ai593423625]

学习···

[mytest114]

[Quote=引用 1 楼 jstoic 的回复:]

SQL code

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
s……
[/Quote]

感谢jstoic,呵呵,费心了.