将ParentID结构形式的表转换成以OrganID为形式的表
?现有一地区表mdCity，结构为：
ID? ParentID Name
1??? 0??? 安徽省???????????????????????
2??? 1??? 安庆市???????????????????????
3??? 2??? 大观区???????????????????????
4??? 2??? 扬江区???????????????????????
5??? 2??? 宜秀区???????????????????????
6??? 1??? 蚌埠市?
7??? 0??? 福建省???????????????????????
8??? 7??? 福州市???????????????????????
9??? 8??? 仓山区???????????????????????
10??? 7??? 长乐市???????????????????????
11??? 7??? 福清市???????????????????????
12??? 11??? 鼓楼区??

需转换为：???
ID? OrganID Name
1??? 01??? 安徽省???????????????????????
2??? 0101??? 安庆市???????????????????????
3??? 010101??? 大观区???????????????????????
4??? 010102??? 扬江区???????????????????????
5??? 010103??? 宜秀区???????????????????????
6??? 0102??? 蚌埠市?
7??? 02??? 福建省???????????????????????
8??? 0201??? 福州市???????????????????????
9??? 020101??? 仓山区???????????????????????
10??? 0202??? 长乐市???????????????????????
11??? 0203??? 福清市???????????????????????
12??? 020301??? 鼓楼区??

构造以下Sql语句即可：
WITH wmdCity AS (
??? SELECT a.id,a.ParentID,a.Name, convert(varchar(50),RIGHT(‘0’ + convert(varchar(2),ROW_NUMBER () OVER ( ORDER BY? a.ID)),2)) organid
??? FROM mdCity a(NOLOCK)
??? WHERE a.ParentID = 0
??? UNION ALL
??? SELECT a.id,a.ParentID,a.Name,? convert(varchar(50),mc.organid + RIGHT(‘0’ + convert(varchar(2),ROW_NUMBER () OVER ( ORDER BY a.ID)),2))? organid
??? FROM mdCity a(NOLOCK)
??? JOIN wmdCity mc ON a.ParentID = mc.id
)
SELECT * from wmdcity mc
ORDER BY mc.organid