本文共 36364 字，大约阅读时间需要 121 分钟。
我们在日常工作中，经常会建立一些无限级分类的表，例如有地域信息、各种分类信息、品目条目等等
以地域信息为例，我们很少说，建立多个表，比如省一个表，市一个表，县一个表这样的，一般都是放到一个表
本例的表结构如下，可根据自己的表结构自行调整之后的内容
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[dict_cities]( [city_id] [int] IDENTITY(1,1) NOT NULL, -- id标示 [city_name] [nvarchar](50) NOT NULL, -- 地域名称，例如北京、天津、河北、海口、昌平等 [city_parent] [int] NOT NULL, -- 父级id [city_state] [bit] NOT NULL, -- 是否上线 [city_order] [int] NOT NULL, -- 排序数字 [city_path] [varchar](50) NOT NULL, -- 使用触发器自动更新的路径信息 [city_code] [varchar](10) NULL, -- 行政区划编号 [city_del] [bit] NOT NULL, -- 是否删除 [targetId] [int] NULL, -- 合并到的地域id [abbr] [varchar](10) NULL, -- 简称，例如门源回族自治县，这里接填写门源 CONSTRAINT [PK_DICT_CITIES] PRIMARY KEY CLUSTERED ( [city_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[dict_cities] ADD DEFAULT ((0)) FOR [city_parent]GOALTER TABLE [dbo].[dict_cities] ADD DEFAULT ((1)) FOR [city_state]GOALTER TABLE [dbo].[dict_cities] ADD DEFAULT ((0)) FOR [city_order]GOALTER TABLE [dbo].[dict_cities] ADD DEFAULT ((0)) FOR [city_del]GO
然后我们就可以把地域信息填进这个表了
然后建立一个触发器，用来更新city_path列
CREATE TRIGGER [dbo].[refresh_citypath_insert] ON [dbo].[dict_cities] AFTER insert,updateAS BEGIN SET NOCOUNT ON; if (select count(0) from inserted)=0 return with parent(city_id,city_parent,city_path,city_order) as ( select city_id,city_parent,convert(varchar(max),'0|'+convert(varchar,city_id)) as city_path,city_order from dict_cities where city_parent=0 union all select ci.city_id,ci.city_parent,convert(varchar(max),p.city_path+'|'+convert(varchar,ci.city_id)),p.city_order from dict_cities ci,parent p where ci.city_parent=p.city_id ) update dict_cities set city_path=p.city_path,city_order=p.rowid from ( select *,row_number() over(order by city_order,city_path) as rowid from parent ) p where dict_cities.city_id=p.city_idEND
city_path将作为地域的层级依据和归属依据在后边要用到哦，该字段为必要字段
数据内容类似以下结果
city_id city_name city_parent city_state city_order city_path city_code city_del targetId abbr----------- -------------------------------------------------- ----------- ---------- ----------- -------------------------------------------------- ---------- -------- ----------- ----------212 华南 0 1 896 0|212 0 NULL NULL19 广东 212 1 897 0|212|19 440000 0 NULL gd3351 广州 19 1 898 0|212|19|3351 440100 0 NULL NULL3372 越秀区 3351 1 899 0|212|19|3351|3372 440104 0 NULL NULL3373 南沙区 3351 1 900 0|212|19|3351|3373 440115 0 NULL NULL3374 海珠区 3351 1 901 0|212|19|3351|3374 440105 0 NULL NULL3375 荔湾区 3351 1 902 0|212|19|3351|3375 440103 0 NULL NULL3376 天河区 3351 1 903 0|212|19|3351|3376 440106 0 NULL NULL3377 白云区 3351 1 904 0|212|19|3351|3377 440111 0 NULL NULL3378 黄埔区 3351 1 905 0|212|19|3351|3378 440112 0 NULL NULL3380 花都区 3351 1 906 0|212|19|3351|3380 440114 0 NULL NULL3381 番禺区 3351 1 907 0|212|19|3351|3381 440113 0 NULL NULL3382 从化区 3351 1 908 0|212|19|3351|3382 440117 0 NULL NULL3383 增城区 3351 1 909 0|212|19|3351|3383 440118 0 NULL NULL3352 深圳 19 1 910 0|212|19|3352 440300 0 NULL NULL3384 福田区 3352 1 911 0|212|19|3352|3384 440304 0 NULL NULL3385 罗湖区 3352 1 912 0|212|19|3352|3385 440303 0 NULL NULL3386 南山区 3352 1 913 0|212|19|3352|3386 440305 0 NULL NULL3387 宝安区 3352 1 914 0|212|19|3352|3387 440306 0 NULL NULL3388 龙岗区 3352 1 915 0|212|19|3352|3388 440307 0 NULL NULL3389 盐田区 3352 1 916 0|212|19|3352|3389 440308 0 NULL NULL3353 珠海 19 1 917 0|212|19|3353 440400 0 NULL NULL3390 香洲区 3353 1 918 0|212|19|3353|3390 440402 0 NULL NULL3391 斗门区 3353 1 919 0|212|19|3353|3391 440403 0 NULL NULL3392 金湾区 3353 1 920 0|212|19|3353|3392 440404 0 NULL NULL3354 汕头 19 1 921 0|212|19|3354 440500 0 NULL NULL3393 潮阳区 3354 1 922 0|212|19|3354|3393 440513 0 NULL NULL3394 潮南区 3354 1 923 0|212|19|3354|3394 440514 0 NULL NULL3395 澄海区 3354 1 924 0|212|19|3354|3395 440515 0 NULL NULL3396 濠江区 3354 1 925 0|212|19|3354|3396 440512 0 NULL NULL3397 金平区 3354 1 926 0|212|19|3354|3397 440511 0 NULL NULL3398 龙湖区 3354 1 927 0|212|19|3354|3398 440507 0 NULL NULL3399 南澳县 3354 1 928 0|212|19|3354|3399 440523 0 NULL NULL3355 韶关 19 1 929 0|212|19|3355 440200 0 NULL NULL3401 浈江区 3355 1 930 0|212|19|3355|3401 440204 0 NULL NULL3402 武江区 3355 1 931 0|212|19|3355|3402 440203 0 NULL NULL3403 乐昌 3355 1 932 0|212|19|3355|3403 440281 0 NULL NULL3404 南雄 3355 1 933 0|212|19|3355|3404 440282 0 NULL NULL3405 仁化县 3355 1 934 0|212|19|3355|3405 440224 0 NULL NULL3406 始兴县 3355 1 935 0|212|19|3355|3406 440222 0 NULL NULL3407 翁源县 3355 1 936 0|212|19|3355|3407 440229 0 NULL NULL3408 曲江区 3355 1 937 0|212|19|3355|3408 440205 0 NULL NULL3409 新丰县 3355 1 938 0|212|19|3355|3409 440233 0 NULL NULL3410 乳源瑶族自治县 3355 1 939 0|212|19|3355|3410 440232 0 NULL 乳源3356 河源 19 1 940 0|212|19|3356 441600 0 NULL NULL3411 源城区 3356 1 941 0|212|19|3356|3411 441602 0 NULL NULL3412 和平县 3356 1 942 0|212|19|3356|3412 441624 0 NULL NULL3413 龙川县 3356 1 943 0|212|19|3356|3413 441622 0 NULL NULL3414 紫金县 3356 1 944 0|212|19|3356|3414 441621 0 NULL NULL3415 连平县 3356 1 945 0|212|19|3356|3415 441623 0 NULL NULL3416 东源县 3356 1 946 0|212|19|3356|3416 441625 0 NULL NULL3357 梅州 19 1 947 0|212|19|3357 441400 0 NULL NULL3417 梅江区 3357 1 948 0|212|19|3357|3417 441402 0 NULL NULL3418 兴宁 3357 1 949 0|212|19|3357|3418 441481 0 NULL NULL3419 梅县区 3357 1 950 0|212|19|3357|3419 441403 0 NULL NULL3420 蕉岭县 3357 1 951 0|212|19|3357|3420 441427 0 NULL NULL3421 大埔县 3357 1 952 0|212|19|3357|3421 441422 0 NULL NULL3422 丰顺县 3357 1 953 0|212|19|3357|3422 441423 0 NULL NULL3423 五华县 3357 1 954 0|212|19|3357|3423 441424 0 NULL NULL3660 平远县 3357 1 955 0|212|19|3357|3660 441426 0 NULL NULL3358 惠州 19 1 956 0|212|19|3358 441300 0 NULL NULL3424 惠城区 3358 1 957 0|212|19|3358|3424 441302 0 NULL NULL3425 惠阳区 3358 1 958 0|212|19|3358|3425 441303 0 NULL NULL3426 惠东县 3358 1 959 0|212|19|3358|3426 441323 0 NULL NULL3427 博罗县 3358 1 960 0|212|19|3358|3427 441322 0 NULL NULL3428 龙门县 3358 1 961 0|212|19|3358|3428 441324 0 NULL NULL3359 汕尾 19 1 962 0|212|19|3359 441500 0 NULL NULL3429 城区 3359 1 963 0|212|19|3359|3429 441502 0 NULL NULL3430 陆丰 3359 1 964 0|212|19|3359|3430 441581 0 NULL NULL3431 海丰县 3359 1 965 0|212|19|3359|3431 441521 0 NULL NULL3432 陆河县 3359 1 966 0|212|19|3359|3432 441523 0 NULL NULL3360 东莞 19 1 967 0|212|19|3360 441900 0 NULL NULL3361 中山 19 1 968 0|212|19|3361 442000 0 NULL NULL3362 江门 19 1 969 0|212|19|3362 440700 0 NULL NULL3433 江海区 3362 1 970 0|212|19|3362|3433 440704 0 NULL NULL3434 蓬江区 3362 1 971 0|212|19|3362|3434 440703 0 NULL NULL3435 新会区 3362 1 972 0|212|19|3362|3435 440705 0 NULL NULL3436 台山 3362 1 973 0|212|19|3362|3436 440781 0 NULL NULL3437 开平 3362 1 974 0|212|19|3362|3437 440783 0 NULL NULL3438 鹤山 3362 1 975 0|212|19|3362|3438 440784 0 NULL NULL3439 恩平 3362 1 976 0|212|19|3362|3439 440785 0 NULL NULL3363 佛山 19 1 977 0|212|19|3363 440600 0 NULL NULL3440 顺德区 3363 1 978 0|212|19|3363|3440 440606 0 NULL NULL3441 南海区 3363 1 979 0|212|19|3363|3441 440605 0 NULL NULL3442 三水区 3363 1 980 0|212|19|3363|3442 440607 0 NULL NULL3443 高明区 3363 1 981 0|212|19|3363|3443 440608 0 NULL NULL3444 禅城区 3363 1 982 0|212|19|3363|3444 440604 0 NULL NULL3364 阳江 19 1 983 0|212|19|3364 441700 0 NULL NULL3445 江城区 3364 1 984 0|212|19|3364|3445 441702 0 NULL NULL3446 阳春 3364 1 985 0|212|19|3364|3446 441781 0 NULL NULL3447 阳西县 3364 1 986 0|212|19|3364|3447 441721 0 NULL NULL3448 阳东区 3364 1 987 0|212|19|3364|3448 441704 0 NULL NULL3365 湛江 19 1 988 0|212|19|3365 440800 0 NULL NULL3449 赤坎区 3365 1 989 0|212|19|3365|3449 440802 0 NULL NULL3450 霞山区 3365 1 990 0|212|19|3365|3450 440803 0 NULL NULL3451 坡头区 3365 1 991 0|212|19|3365|3451 440804 0 NULL NULL3452 麻章区 3365 1 992 0|212|19|3365|3452 440811 0 NULL NULL3453 廉江 3365 1 993 0|212|19|3365|3453 440881 0 NULL NULL3454 雷州 3365 1 994 0|212|19|3365|3454 440882 0 NULL NULL3455 吴川 3365 1 995 0|212|19|3365|3455 440883 0 NULL NULL3456 遂溪县 3365 1 996 0|212|19|3365|3456 440823 0 NULL NULL3457 徐闻县 3365 1 997 0|212|19|3365|3457 440825 0 NULL NULL3366 茂名 19 1 998 0|212|19|3366 440900 0 NULL NULL3458 茂南区 3366 1 999 0|212|19|3366|3458 440902 0 NULL NULL3460 高州 3366 1 1000 0|212|19|3366|3460 440981 0 NULL NULL3461 化州 3366 1 1001 0|212|19|3366|3461 440982 0 NULL NULL3462 信宜 3366 1 1002 0|212|19|3366|3462 440983 0 NULL NULL3463 电白区 3366 1 1003 0|212|19|3366|3463 440904 0 NULL NULL3367 肇庆 19 1 1004 0|212|19|3367 441200 0 NULL NULL3464 端州区 3367 1 1005 0|212|19|3367|3464 441202 0 NULL NULL3465 鼎湖区 3367 1 1006 0|212|19|3367|3465 441203 0 NULL NULL3466 高要区 3367 1 1007 0|212|19|3367|3466 441204 0 NULL NULL3467 四会 3367 1 1008 0|212|19|3367|3467 441284 0 NULL NULL3468 广宁县 3367 1 1009 0|212|19|3367|3468 441223 0 NULL NULL3469 德庆县 3367 1 1010 0|212|19|3367|3469 441226 0 NULL NULL3470 封开县 3367 1 1011 0|212|19|3367|3470 441225 0 NULL NULL3471 怀集县 3367 1 1012 0|212|19|3367|3471 441224 0 NULL NULL3368 清远 19 1 1013 0|212|19|3368 441800 0 NULL NULL3472 清城区 3368 1 1014 0|212|19|3368|3472 441802 0 NULL NULL3473 英德 3368 1 1015 0|212|19|3368|3473 441881 0 NULL NULL3474 连州 3368 1 1016 0|212|19|3368|3474 441882 0 NULL NULL3475 佛冈县 3368 1 1017 0|212|19|3368|3475 441821 0 NULL NULL3476 阳山县 3368 1 1018 0|212|19|3368|3476 441823 0 NULL NULL3477 清新区 3368 1 1019 0|212|19|3368|3477 441803 0 NULL NULL3478 连山壮族瑶族自治县 3368 1 1020 0|212|19|3368|3478 441825 0 NULL 连山3479 连南瑶族自治县 3368 1 1021 0|212|19|3368|3479 441826 0 NULL 连南3369 潮州 19 1 1022 0|212|19|3369 445100 0 NULL NULL3480 湘桥区 3369 1 1023 0|212|19|3369|3480 445102 0 NULL NULL3481 潮安区 3369 1 1024 0|212|19|3369|3481 445103 0 NULL NULL3482 饶平县 3369 1 1025 0|212|19|3369|3482 445122 0 NULL NULL3370 揭阳 19 1 1026 0|212|19|3370 445200 0 NULL NULL3483 榕城区 3370 1 1027 0|212|19|3370|3483 445202 0 NULL NULL3484 普宁 3370 1 1028 0|212|19|3370|3484 445281 0 NULL NULL3485 揭东区 3370 1 1029 0|212|19|3370|3485 445203 0 NULL NULL3486 揭西县 3370 1 1030 0|212|19|3370|3486 445222 0 NULL NULL3487 惠来县 3370 1 1031 0|212|19|3370|3487 445224 0 NULL NULL3371 云浮 19 1 1032 0|212|19|3371 445300 0 NULL NULL3488 云城区 3371 1 1033 0|212|19|3371|3488 445302 0 NULL NULL3489 罗定 3371 1 1034 0|212|19|3371|3489 445381 0 NULL NULL3490 云安区 3371 1 1035 0|212|19|3371|3490 445303 0 NULL NULL3491 新兴县 3371 1 1036 0|212|19|3371|3491 445321 0 NULL NULL3492 郁南县 3371 1 1037 0|212|19|3371|3492 445322 0 NULL NULL20 海南 212 1 1038 0|212|20 460000 0 NULL han3493 海口 20 1 1039 0|212|20|3493 460100 0 NULL NULL3496 秀英区 3493 1 1040 0|212|20|3493|3496 460105 0 NULL NULL3497 龙华区 3493 1 1041 0|212|20|3493|3497 460106 0 NULL NULL3498 琼山区 3493 1 1042 0|212|20|3493|3498 460107 0 NULL NULL3499 美兰区 3493 1 1043 0|212|20|3493|3499 460108 0 NULL NULL3494 三亚 20 1 1044 0|212|20|3494 460200 0 NULL NULL3667 海棠区 3494 1 1045 0|212|20|3494|3667 460202 0 NULL NULL3668 吉阳区 3494 1 1046 0|212|20|3494|3668 460203 0 NULL NULL3669 天涯区 3494 1 1047 0|212|20|3494|3669 460204 0 NULL NULL3670 崖州区 3494 1 1048 0|212|20|3494|3670 460205 0 NULL NULL3500 五指山 20 1 1049 0|212|20|3500 469001 0 NULL NULL3501 琼海 20 1 1050 0|212|20|3501 469002 0 NULL NULL3502 儋州 20 1 1051 0|212|20|3502 460400 0 NULL NULL3503 三沙 20 1 1052 0|212|20|3503 460300 0 NULL NULL3504 文昌 20 1 1053 0|212|20|3504 469005 0 NULL NULL3505 万宁 20 1 1054 0|212|20|3505 469006 0 NULL NULL3506 东方 20 1 1055 0|212|20|3506 469007 0 NULL NULL3507 澄迈县 20 1 1056 0|212|20|3507 469023 0 NULL NULL3508 定安县 20 1 1057 0|212|20|3508 469021 0 NULL NULL3509 屯昌县 20 1 1058 0|212|20|3509 469022 0 NULL NULL3510 临高县 20 1 1059 0|212|20|3510 469024 0 NULL NULL3511 白沙黎族自治县 20 1 1060 0|212|20|3511 469025 0 NULL 白沙3512 昌江黎族自治县 20 1 1061 0|212|20|3512 469026 0 NULL 昌江3513 乐东黎族自治县 20 1 1062 0|212|20|3513 469027 0 NULL 乐东3514 陵水黎族自治县 20 1 1063 0|212|20|3514 469028 0 NULL 陵水3515 保亭黎族苗族自治县 20 1 1064 0|212|20|3515 469029 0 NULL 保亭3516 琼中黎族苗族自治县 20 1 1065 0|212|20|3516 469030 0 NULL 琼中21 广西 212 1 1066 0|212|21 450000 0 NULL gx3517 南宁 21 1 1067 0|212|21|3517 450100 0 NULL NULL3532 青秀区 3517 1 1068 0|212|21|3517|3532 450103 0 NULL NULL3533 兴宁区 3517 1 1069 0|212|21|3517|3533 450102 0 NULL NULL3534 西乡塘区 3517 1 1070 0|212|21|3517|3534 450107 0 NULL NULL3535 良庆区 3517 1 1071 0|212|21|3517|3535 450108 0 NULL NULL3536 江南区 3517 1 1072 0|212|21|3517|3536 450105 0 NULL NULL3537 邕宁区 3517 1 1073 0|212|21|3517|3537 450109 0 NULL NULL3538 武鸣区 3517 1 1074 0|212|21|3517|3538 450110 0 NULL NULL3539 隆安县 3517 1 1075 0|212|21|3517|3539 450123 0 NULL NULL3540 马山县 3517 1 1076 0|212|21|3517|3540 450124 0 NULL NULL3541 上林县 3517 1 1077 0|212|21|3517|3541 450125 0 NULL NULL3542 宾阳县 3517 1 1078 0|212|21|3517|3542 450126 0 NULL NULL3543 横县 3517 1 1079 0|212|21|3517|3543
转载地址：http://lxvxi.baihongyu.com/