如果我要将下面的数字构建成树该怎么做？
11218132450000
11219132451000
11220132452000
11221132452100
11222132453000
11223132454000
11224132455000
11225132455100
11226132456000
11227132458000
11228132457000
11229132460000
11230132461000
11231132463000
11232132462000
11233132464000
11234132500000
11235132600000
11236132800000
11237132900000
11238132A00000
11239132B00000
11240132D00000
11241132E00000
11242132E10000
11243132E90000
11244132E20000
11245132EA0000
11246132E30000
11247132EB0000
11248132E40000
11249132EC0000
11250132E50000
11251132E60000
11252132E70000
11253132E80000
11254132ED0000
11255132EE0000
11256132EF0000
11257132EG0000
11258132EH0000
11259132F00000
11260132F10000
11261132F20000
11262132H00000
11263132I00000
11264132J00000
11265132L00000
11266132M00000
11267132R00000
11268132S00000
11269132N00000
11270132N30000
11271132N50000
11272132N80000
11273132ND0000
11274132NE0000
11275132NF0000
11276132NG0000
11277132O00000
11278132P00000
11279132P10000
11280132P20000
11281132P30000
112821FB000001
11283133100000
11284133200000
11285116100000
11286116200000
11287000000000
11288000000001
11289000000002
11290000000004
112911FB000002
112921FB000003
112931FB000004
112941FB000005
112951FB000006
112961FB000007
112971FB000008
112981FB000009
12001111000000
12002111100000
12003111110000
12004111120000
12005111130000
12006111140000
12007111141000
12008111142000
12009111143000
12010111144000
12012111160000
12013111161000
12014111161100
12015111161200
12016111161300
12017111161310
12018111161320
12019111161400
12020111161500
12021111162000
12022111163000
12023111163100
12024111163200
12025111163300
12026111163400
12027111163500
12028111164000
12029111164100
12030111164200
12031111165000
12032111170000
1203311117G000
1203411117A000
1203511117G200
1203611117H000
12037111180000
12038111181000
12039111181100
12040111181200
12041111181300
12042111181310
12043111181320
12044111181400
12045111181500
12046111181510
12047111182000
12048111183000
12049111183100
12050111183200
12051111183300
12052111183310
12053111183400
12054111183600
12055111183700
12056111183500
12057111184000
12058111184100
12059111184200
12060111190000
12061111191000
12062111192000
12063111193000
12064111194000
12065111194200
12066111194300
12067111194400
12068111194500
12069111194600
12070111195000
12071111196000
12072111197000
12073111198000
12074111198100
12075111198500
12076111198510
12077111198520
12078111198600
12079111198700
12080111198800
12081111198900
12082111198A00
12083111198B00
12084111198C00
12085111198D00
12086111198E00
12087111198K00
12088111198F00
12089111198F10
12090111198F70
12091111198F20
12092111198F30
12093111198F40
12094111198F50
12095111198F60
12096111198H00
12097111198I00
12098111198J00
120991111A0000
121001111A1000
121011111A2000
121021111A3000
121031111A3100
121041111A3110
121051111A3120
121061111A3200
121071111A3300
121081111A3310
121091111A3320
121101111A3400
121111111A3410
121121111A3420
121131111A3500
121141111A3520
121151111A3510
121161111A4000
121171111A5000
122311111K0000
122321111K1000
122331111K2000
122341111K3000
122351111K4000
12236111200000
12237111210000
12238111211000
12239111212000
12240111217000
12241111216000
12242111216I00
12243111216I10
12244111216I70
12245111216I20
12246111216I30
12247111216I40
12248111216I50
12249111216I60
12250111216J00
12251111216300
12252111216400
12253111216500
12254111216510
12255111216600
12256111216610
12257111216620
12258111216630
12259111216700
12260111216800
12261111216900
12262111216A00
12263111216B00
12264111216C00
12265111216D00
12266111216E00
12267111216G00
12268111216H00
12269111216F00
12270111230000
12271111231000
12272111232000
12273111233000
12274111234000
12275111235000
12276111240000
12277111241000
12278111242000
12279111250000
12280111251000
12281111252000
12282111252100
12283111253000
12284111253100
12285111253110
12286111253120
12287111253200
12288111253300
12289111253310
12290111253320
12291111253400
12292111253410
12293111253420
12294111253500
12295111254000
12296111255000
12297111255100
12298111255200
12299111255300
12300111256000
12301111270000
12302111271000
12303111272000
12304111280000
12305111281000
12306111282000
12307111290000
123081112A0000
123091112B0000
123101112C0000
123111112D0000
123121112E0000
123131112E1000
123141112E2000
123151112E3000
123161112E4000
123171112E8000
123181112E5000
123191112E6000
123201112E7000
123211112F0000
123221112F1000
123231112F1100
123241112F1200
123251112F1300
123261112F1400
123271112F1500
123281112F1600
123291112F1700
123301112F1800
123311112F1900
123321112F1A00
123331112F1B00
123341112F2000
123351112F2100
123361112F2200
123371112F2300
123381112F2400
123391112F2500
123401112F2600
12341113000000
12342113100000
12343113110000
12344113111000
12345113112000
12346113113000
12347113114000
12348113115000
12349113116000
这个必须按第一列排序。。。。
[解决办法]
create table tt(co1 int,co2 varchar(20))
insert into tt select 11218, '132450000 '
union all select 11219, '132451000 '
union all select 11220, '132452000 '
union all select 11221, '132452100 '
union all select 11222, '132453000 '
union all select 11223, '132454000 '
union all select 11224, '132455000 '
union all select 11225, '132455100 '
union all select 11226, '132456000 '
union all select 11227, '132458000 '
union all select 11228, '132457000 '
union all select 11229, '132460000 '
union all select 11230, '132461000 '
union all select 11231, '132463000 '
union all select 11232, '132462000 '
union all select 11233, '132464000 '
union all select 11234, '132500000 '
union all select 11235, '132600000 '
union all select 11236, '132800000 '
union all select 11237, '132900000 '
union all select 11238, '132A00000 '
union all select 11239, '132B00000 '
union all select 11240, '132D00000 '
union all select 11241, '132E00000 '
union all select 11242, '132E10000 '
union all select 11243, '132E90000 '
union all select 11244, '132E20000 '
union all select 11245, '132EA0000 '
union all select 11246, '132E30000 '
union all select 11247, '132EB0000 '
union all select 11248, '132E40000 '
union all select 11249, '132EC0000 '
union all select 11250, '132E50000 '
union all select 11251, '132E60000 '
union all select 11252, '132E70000 '
union all select 11253, '132E80000 '
union all select 11254, '132ED0000 '
union all select 11255, '132EE0000 '
union all select 11256, '132EF0000 '
union all select 11257, '132EG0000 '
union all select 11258, '132EH0000 '
union all select 11259, '132F00000 '
union all select 11260, '132F10000 '
union all select 11261, '132F20000 '
union all select 11262, '132H00000 '
union all select 11263, '132I00000 '
union all select 11264, '132J00000 '
union all select 11265, '132L00000 '
union all select 11266, '132M00000 '
union all select 11267, '132R00000 '
union all select 11268, '132S00000 '
union all select 11269, '132N00000 '
union all select 11270, '132N30000 '
union all select 11271, '132N50000 '
union all select 11272, '132N80000 '
union all select 11273, '132ND0000 '
union all select 11274, '132NE0000 '
union all select 11275, '132NF0000 '
union all select 11276, '132NG0000 '
union all select 11277, '132O00000 '
union all select 11278, '132P00000 '
union all select 11279, '132P10000 '
union all select 11280, '132P20000 '
union all select 11281, '132P30000 '
union all select 11282, '1FB000001 '
union all select 11283, '133100000 '
union all select 11284, '133200000 '
union all select 11285, '116100000 '
union all select 11286, '116200000 '
union all select 11287, '000000000 '
union all select 11288, '000000001 '
union all select 11289, '000000002 '
union all select 11290, '000000004 '
union all select 11291, '1FB000002 '
union all select 11292, '1FB000003 '
union all select 11293, '1FB000004 '
union all select 11294, '1FB000005 '
union all select 11295, '1FB000006 '
union all select 11296, '1FB000007 '
union all select 11297, '1FB000008 '
union all select 11298, '1FB000009 '
union all select 12001, '111000000 '
union all select 12002, '111100000 '
union all select 12003, '111110000 '
union all select 12004, '111120000 '
union all select 12005, '111130000 '
union all select 12006, '111140000 '
union all select 12007, '111141000 '
union all select 12008, '111142000 '
union all select 12009, '111143000 '
union all select 12010, '111144000 '
union all select 12012, '111160000 '
union all select 12013, '111161000 '
union all select 12014, '111161100 '
union all select 12015, '111161200 '
union all select 12016, '111161300 '
union all select 12017, '111161310 '
union all select 12018, '111161320 '
union all select 12019, '111161400 '
union all select 12020, '111161500 '
union all select 12021, '111162000 '
union all select 12022, '111163000 '
union all select 12023, '111163100 '
union all select 12024, '111163200 '
union all select 12025, '111163300 '
union all select 12026, '111163400 '
union all select 12027, '111163500 '
union all select 12028, '111164000 '
union all select 12029, '111164100 '
union all select 12030, '111164200 '
union all select 12031, '111165000 '
union all select 12032, '111170000 '
union all select 12033, '11117G000 '
union all select 12034, '11117A000 '
union all select 12035, '11117G200 '
union all select 12036, '11117H000 '
union all select 12037, '111180000 '
union all select 12038, '111181000 '
union all select 12039, '111181100 '
union all select 12040, '111181200 '
union all select 12041, '111181300 '
union all select 12042, '111181310 '
union all select 12043, '111181320 '
union all select 12044, '111181400 '
union all select 12045, '111181500 '
union all select 12046, '111181510 '
union all select 12047, '111182000 '
union all select 12048, '111183000 '
union all select 12049, '111183100 '
union all select 12050, '111183200 '
union all select 12051, '111183300 '
union all select 12052, '111183310 '
union all select 12053, '111183400 '
union all select 12054, '111183600 '
union all select 12055, '111183700 '
union all select 12056, '111183500 '
union all select 12057, '111184000 '
union all select 12058, '111184100 '
union all select 12059, '111184200 '
union all select 12060, '111190000 '
union all select 12061, '111191000 '
union all select 12062, '111192000 '
select isnull(cast(b.co1 as varchar(10)), ' ') '根 ',
isnull(cast(a.co1 as varchar(10)), ' ') 'co1 ',
isnull(cast(a.co2 as varchar(10)), ' ') 'co2 '
from tt a full join
(select co1/100 'co1 ' from tt group by co1/100)b
on b.co1=a.co1
order by isnull(b.co1,a.co1/100)
[解决办法]
必须按第一列排序?
declare @tt table(col1 int,col2 varchar(20))
insert into @tt select 11218, '132450000 '
union all select 11219, '132451000 '
union all select 11220, '132452000 '
union all select 11221, '132452100 '
union all select 11222, '132453000 '
union all select 11223, '132454000 '
union all select 11224, '132455000 '
union all select 11225, '132455100 '
union all select 11226, '132456000 '
union all select 11227, '132458000 '
union all select 11228, '132457000 '
union all select 11229, '132460000 '
union all select 11230, '132461000 '
union all select 11231, '132463000 '
union all select 11232, '132462000 '
union all select 11233, '132464000 '
union all select 11234, '132500000 '
union all select 11235, '132600000 '
union all select 11236, '132800000 '
union all select 11237, '132900000 '
union all select 11238, '132A00000 '
union all select 11239, '132B00000 '
union all select 11240, '132D00000 '
union all select 11241, '132E00000 '
union all select 11242, '132E10000 '
union all select 11243, '132E90000 '
union all select 11244, '132E20000 '
union all select 11245, '132EA0000 '
union all select 11246, '132E30000 '
union all select 11247, '132EB0000 '
union all select 11248, '132E40000 '
union all select 11249, '132EC0000 '
union all select 11250, '132E50000 '
union all select 11251, '132E60000 '
union all select 11252, '132E70000 '
union all select 11253, '132E80000 '
union all select 11254, '132ED0000 '
union all select 11255, '132EE0000 '
union all select 11256, '132EF0000 '
union all select 11257, '132EG0000 '
union all select 11258, '132EH0000 '
union all select 11259, '132F00000 '
union all select 11260, '132F10000 '
union all select 11261, '132F20000 '
union all select 11262, '132H00000 '
union all select 11263, '132I00000 '
union all select 11264, '132J00000 '
union all select 11265, '132L00000 '
union all select 11266, '132M00000 '
union all select 11267, '132R00000 '
union all select 11268, '132S00000 '
union all select 11269, '132N00000 '
union all select 11270, '132N30000 '
union all select 11271, '132N50000 '
union all select 11272, '132N80000 '
union all select 11273, '132ND0000 '
union all select 11274, '132NE0000 '
union all select 11275, '132NF0000 '
union all select 11276, '132NG0000 '
union all select 11277, '132O00000 '
union all select 11278, '132P00000 '
union all select 11279, '132P10000 '
union all select 11280, '132P20000 '
union all select 11281, '132P30000 '
union all select 11282, '1FB000001 '
union all select 11283, '133100000 '
union all select 11284, '133200000 '
union all select 11285, '116100000 '
union all select 11286, '116200000 '
union all select 11287, '000000000 '
union all select 11288, '000000001 '
union all select 11289, '000000002 '
union all select 11290, '000000004 '
union all select 11291, '1FB000002 '
union all select 11292, '1FB000003 '
union all select 11293, '1FB000004 '
union all select 11294, '1FB000005 '
union all select 11295, '1FB000006 '
union all select 11296, '1FB000007 '
union all select 11297, '1FB000008 '
union all select 11298, '1FB000009 '
union all select 12001, '111000000 '
union all select 12002, '111100000 '
union all select 12003, '111110000 '
union all select 12004, '111120000 '
union all select 12005, '111130000 '
union all select 12006, '111140000 '
union all select 12007, '111141000 '
union all select 12008, '111142000 '
union all select 12009, '111143000 '
union all select 12010, '111144000 '
union all select 12012, '111160000 '
union all select 12013, '111161000 '
union all select 12014, '111161100 '
union all select 12015, '111161200 '
union all select 12016, '111161300 '
union all select 12017, '111161310 '
union all select 12018, '111161320 '
union all select 12019, '111161400 '
union all select 12020, '111161500 '
union all select 12021, '111162000 '
union all select 12022, '111163000 '
union all select 12023, '111163100 '
union all select 12024, '111163200 '
union all select 12025, '111163300 '
union all select 12026, '111163400 '
union all select 12027, '111163500 '
union all select 12028, '111164000 '
union all select 12029, '111164100 '
union all select 12030, '111164200 '
union all select 12031, '111165000 '
union all select 12032, '111170000 '
union all select 12033, '11117G000 '
union all select 12034, '11117A000 '
union all select 12035, '11117G200 '
union all select 12036, '11117H000 '
union all select 12037, '111180000 '
union all select 12038, '111181000 '
union all select 12039, '111181100 '
union all select 12040, '111181200 '
union all select 12041, '111181300 '
union all select 12042, '111181310 '
union all select 12043, '111181320 '
union all select 12044, '111181400 '
union all select 12045, '111181500 '
union all select 12046, '111181510 '
union all select 12047, '111182000 '
union all select 12048, '111183000 '
union all select 12049, '111183100 '
union all select 12050, '111183200 '
union all select 12051, '111183300 '
union all select 12052, '111183310 '
union all select 12053, '111183400 '
union all select 12054, '111183600 '
union all select 12055, '111183700 '
union all select 12056, '111183500 '
union all select 12057, '111184000 '
union all select 12058, '111184100 '
union all select 12059, '111184200 '
union all select 12060, '111190000 '
union all select 12061, '111191000 '
union all select 12062, '111192000 '
select
case when left(a.col2,3)=left(b.col2,3) then ' ' else left(a.col2,3)+ '- ' end+
case when left(a.col2,6)=left(b.col2,6) then ' ' else substring(a.col2,4,3)+ '- ' end+
substring(a.col2,7,3)
from @tt a left join @tt b
on b.col1=(select max(col1) from @tt where col1 <a.col1)
order by a.col1
[解决办法]
结果：
----------------------
132┬450─000
├451─000
├452┬000
│ └100
├453─000
├454─000
├455┬000
│ └100
├456─000
├458─000
├457─000
├460─000
├461─000
├463─000
├462─000
├464─000
├500─000
├600─000
├800─000
├900─000
├A00─000
├B00─000
├D00─000
├E00─000
├E10─000
├E90─000
├E20─000
├EA0─000
├E30─000
├EB0─000
├E40─000
├EC0─000
├E50─000
├E60─000
├E70─000
├E80─000
├ED0─000
├EE0─000
├EF0─000
├EG0─000
├EH0─000
├F00─000
├F10─000
├F20─000
├H00─000
├I00─000
├J00─000
├L00─000
├M00─000
├R00─000
├S00─000
├N00─000
├N30─000
├N50─000
├N80─000
├ND0─000
├NE0─000
├NF0─000
├NG0─000
├O00─000
├P00─000
├P10─000
├P20─000
└P30─000
1FB─000─001
133┬100─000
└200─000
116┬100─000
└200─000
000─000┬000
├001
├002
└004
1FB─000┬002
├003
├004
├005
├006
├007
├008
└009
111┬000─000
├100─000
├110─000
├120─000
├130─000
├140─000
├141─000
├142─000
├143─000
├144─000
├160─000
├161┬000
│ ├100
│ ├200
│ ├300
│ ├310
│ ├320
│ ├400
│ └500
├162─000
├163┬000
│ ├100
│ ├200
│ ├300
│ ├400
│ └500
├164┬000
│ ├100
│ └200
├165─000
├170─000
├17G─000
├17A─000
├17G─200
├17H─000
├180─000
├181┬000
│ ├100
│ ├200
│ ├300
│ ├310
│ ├320
│ ├400
│ ├500
│ └510
├182─000
├183┬000
│ ├100
│ ├200
│ ├300
│ ├310
│ ├400
│ ├600
│ ├700
│ └500
├184┬000
│ ├100
│ └200
├190─000
├191─000
└192─000
（所影响的行数为 142 行）
