之前写过关于 菜单树的。 http://www.cnblogs.com/newsea/archive/2012/08/01/2618731.html
现在在写城市树。
结构：
CREATE TABLE [dbo].[S_City](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[PID] [int] NULL,
[Wbs] [varchar](50) NULL,
[Code] [varchar](50) NULL,
[SortID] [float] NULL,
[IsValidate] [bit] NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
用 Excel 导入了数据。 数据是顺序树型的。 PID 是空的， Wbs 是正确的。顺序树的Wbs为： 1 ， 1.1 ， 1.2 ， 2 ， 2.1 ， 2.2 ，2.2.1 ，3 ，3.1 。。。。
需要做的工作：
1. 更新正确的 PID 。
2. 把WBS 更新为 伪WBS， 伪WBS 是 PWbs + "," + PID , 根节点的 WBS = PID
操作过程
用一个基础SQL，得到 父PWbs，Level：
select *,
SUBSTRING(wbs,1, LEN(wbs) - charindex('.', REVERSE( wbs) ) ) PWbs,
LEN(wbs)- len(REPLACE(wbs,'.','')) as [Level]
from dbo.S_City
where LEN(wbs)- len(REPLACE(wbs,'.','')) = 1
再逐级更新PID
--第一步，更新根
update S_City
set pid = 0
where LEN(wbs)- len(REPLACE(wbs,'.','')) = 0 --第二步，更新二级：
update c
set c.pid = p.id
from S_City as c ,S_City as p
where SUBSTRING(c.wbs,1, LEN(c.wbs) - charindex('.', REVERSE( c.wbs) ) ) = p.Wbs and LEN(c.wbs)- len(REPLACE(c.wbs,'.','')) = 1 --第三步，更新第三级 update c
set c.pid = p.id
from S_City as c ,S_City as p
where SUBSTRING(c.wbs,1, LEN(c.wbs) - charindex('.', REVERSE( c.wbs) ) ) = p.Wbs and LEN(c.wbs)- len(REPLACE(c.wbs,'.','')) = 2
验证一下树：
with p as (
select * from S_City where Pid = 0
union all
select t.* from S_City as t join p on ( t.PID = p.ID)
) select * from p
再更新 Wbs
--第一步，更新根
update S_City
set Wbs = ''
where pid = 0 --第二步，更新二级：
update c
set c.Wbs = p.Wbs +',' + cast(p.id as varchar(30))
from S_City as c ,S_City as p
where c.pid = p.ID and p.PID = 0 --第三步，更新第三级 update c
set c.Wbs = p.Wbs +',' + cast(p.id as varchar(30))
from S_City as c ,S_City as p ,S_City as pp
where c.pid = p.ID and p.pid = pp.ID and pp.PID = 0
完成。