这个sql怎么写？

Question

有两个表A、B，其中表A是一棵树，结构如下：
dm，mc，其中dm遵循3-2-2-2编码规则，有如下数据
101     aa
10101   aa1
10102   aa2
102     bb
102     bb1
10202   bb2
，表B记录A的某个属性，自动如下
bm,dm，其中dm和A关联(一个表A中dm最多只能关联3个表B中的bm，并且A中的孩子可以继承其上级的属性），有数据如下
bm1     101
bm2     10101
bm3     10102
bm4 10101
bm1 102
bm2 10201
bm5 10202  现在我希望能得到如下数据
1、
dm bm  （头）
101  bm1
10101 bm1
10101 bm2
10101 bm4
10102 bm1
10102 bm3
102 bm1
10201 bm1
10201  bm2
10202 bm1
10202 bm5
怎么写sql语句？2、将dm所关联的属性bm(最多3个)在行上显示（没有显示为-1），结果如下
dm 属性1 属性2 属性3 （头）
101 bm1 -1 -1
10101 bm1 bm2 bm4
10102 bm1 bm3 -1
102 bm1 -1 -1
10201 bm1 bm2 -1
10202 bm1 bm5 -1
又该怎么写sql？
这个问题困扰o很久了，希望高手能指点一二。 

Answer 1

1、
select A.dm,B.bm from A,B where B.dm like A.dm+'%' order by A.dm,B.bm

Answer 2

2、
select  
    A.dm,
    属性1=max(case when A.dm=B.dm then b.bm else '-1' end),
    属性2=max(case when A.dm=left(B.dm,len(B.dm)-2) then b.bm else '-1' end),
    属性3=max(case when A.dm=left(B.dm,len(B.dm)-4) then b.bm else '-1' end)
from
    A,B
where
    B.dm like A.dm+'%'
group by
    A.dm

Answer 3

多谢libin_ftsafe的回复。第二句运行有如下错误：
服务器: 消息 536，级别 16，状态 3，行 1
向 substring 函数传递了无效的 length 参数。还望继续指教。
另外，第一句要改成A.dm like B.dm+'%' 结果才正确。

Answer 4

路径都写到dm里了,直接ORDERY BY dm就可以了吧

Answer 5

确实存在长度的问题，修改一下：select  
    A.dm,
    属性1=max(case when A.dm=B.dm then b.bm else '-1' end),
    属性2=max(case when len(A.dm)=len(B.dm)-2 then b.bm else '-1' end),
    属性3=max(case when len(A.dm)=len(B.dm)-4 then b.bm else '-1' end)
from
    A,B
where
    B.dm like A.dm+'%'
group by
    A.dm

Answer 6

结果还是不对。
101 bm1 bm4 -1
10101 bm4 -1 -1
10102 bm3 -1 -1
102 bm1 bm5 -1
10201 bm2 -1 -1
10202 bm5 -1 -1
正确结果是
101 bm1 -1 -1
10101 bm1 bm2 bm4
10102 bm1 bm3 -1
102 bm1 -1 -1
10201 bm1 bm2 -1
10202 bm1 bm5 -1
。
case语句中max似乎有问题，-2,-4也不行，还有可能要-6,-8,-10等等可能，不是每一级上只能设置一个关联的属性，而是所有级次上合计不能超过3个。

Answer 7

交查表有点难select distinct t1.dm , t2.bm  from B as t1 
left join B as t2 on charindex(rtrim(t2.dm) ,rtrim(t1.dm)) > 0select distinct  t1.dm , t2.bm  from B as t1 , B as t2 where   charindex(rtrim(t2.dm) ,rtrim(t1.dm)) > 0