数据表插入问题

[dxphero]

我有两个表table1 table2.
table1中有数据 字段dm,字段mc.
table2中没有数据，需要从table1中提取数据插入到table2中
table2中的字段 dm1，mc1，dm2，mc2，dm3，mc3

table1中的dm 值为10，11，1001，1002，1101，100101，100102
table2中需要的是10，mc，1001，mc，100101，mc
10，mc，1001，mc，100102，mc
10，mc, 1002, mc
11 mc 1101
需要的是这种结果！
请大家帮忙

[filebat]

table2表中dm1一定是两位的数字， dm2是四位， dm3是六位？

[lzymagi]

说出规律来啊

[zlp321002]

--没看到什么数据规律...能不能给点测试数据,和你想要的结果!

[dxphero]

对就是 一个2位，一个4位，一个六位

[powerly]

select dm ,mc ,T2.dm2,T2.mc2 ,T3.dm3,T3.mc3 from table1 T1
left outer join
(select dm as dm2,mc as mc2 from table1 where len(dm)= 4) T2
on left(T2.dm2,2) = T1.dm
left outer join
(select dm as dm3,mc as mc3 from table1 where len(dm)= 6) T3
on left(T3.dm3,2) = T1.dm
where len(dm) = 2
不知道 符不符合你的要求！

[powerly]

好像上面的有点问题 改造一下：
--insert table2
select dm ,mc ,T2.dm2,T2.mc2 ,T2.dm3,T2.mc3 from table1
inner join
(
select dm as dm2,mc as mc2 ,T3.dm3,T3.mc3 from table1
left outer join
(select dm as dm3,mc as mc3 from table1 where len(dm)= 6) T3
on left(T3.dm3,4) = dm
where len(dm) = 4
) T2
on left(T2.dm2,2) = dm
where len(dm) = 2

[hglhyy]

还真有难度，帮你ＵＰ

[nm3000]

select fldm as dldm ,flmc as dlmc ,T2.xldm,T2.xlmc ,T2.bjmc,T2.bjdm from bj_fl_zd inner join(select fldm as xldm,flmc as xlmc ,T3.bjdm,T3.bjmc from bj_fl_zd left outer join (select fldm as bjdm,flmc as bjmc from bj_fl_zd where len(fldm)= 6) T3 on left(T3.bjdm,4) =fldm where len(fldm) = 4) T2 on left(T2.xldm,2) = fldm where len(fldm) = 2