一道关于数据库(经典父子级 ID 关联)更新题

昨天，一同事发过来的一道数据库题目，就是哪种经典的父子级 id 在同一数据库表中设计类型。需要在原表中添加一个字段，同时，将该节点的父子级详细信息插入到原表新增的一字段中，具体效果如下图。

areacode 、areaname、parentcode （原表三字段）. content __新增字段，更新该 areacode 下所有父级菜单信息至新增至原表的 content 字段下面，用红线框起来（意思应该讲明白了吧.）

areacode：地区 id areaname：地区介绍 parentcode：父级 areacode （content---将该 areacode 下的所有父级 areaname 拼成 类似：越城区,绍兴市,浙江省 字符串插入）

更新前:

更新后:

在项目中，相对于此种内容，一般会存储在内存中，作为缓存使用，避免频繁连接数据库，带来的性能问题。

       代码就不上了，简单的递归即可实现。

1 先从数据库将该表所有内容取出来，缓存起来。（该表差不多 3000 左右条数据）
2 再写个递归函数，根据每次传入的 areacode（第一步已缓存数据库取出来的整表集合 可转成 dictionary<int,arearegion> 类型），获取其 parentcode 值，只要 parentcode 不为 0（即不为*结点，parentcode 为*结点），那么，继续调用该函数，传入本次查询实体的 parentcode 信息。
3  在代码很方便查出父结点所有信息，再 update 至数据库即可。

  感谢 清海扬波  grayboy 完全可以通过公用表表达式完成

code 如下：

with cte(areacode,areaname,content) as
(
--- 查询 parentcode 为 0 （parentcode 为 0 的为*菜单）
select areacode,areaname,cast(areaname as varchar(50)) as content
from [arearegion] where parentcode=0
union all
-- 再进行递归查询 
select a.areacode,a.areaname,cast(a.content+','+b.areaname as varchar(50） as content 9 from [arearegion] a 10 inner join cte b on a.parentcode=b.areacode )
select * from cte

--- 楼主当初想法（我想复杂了 sb 了 ）

1 楼主，首当其冲想到的是，即采用游标存储整表记录。

2 游标每次取得一条记录的 areacode ，再通过该 areacode 查出其所有父级表（包括自身）信息，最后，将查询出的该表某字段拼成字符串，

插入表变量中（两字段分别为：areacode、content）。

3 最后，将定义的表变量与原表通过 inner join（表变量中插入了 areacode 字段），进行联表更新。

更新脚本具体如下：

use jkcrm
go
--定义游标
declare updatecursor cursor scroll for
select a.areacode  from dbo.arearegion a
--打开游标
open updatecursor
--定义变量 存储依次获取游标值
declare @aid nvarchar(30)=''
---定义变量 存储父结点具体信息
declare @pstr nvarchar(300)='';
--定义表变量 存储两字段 areacode content ( content :为该字段所有父级菜单信息)
declare @temptable table
(
areacode int primary key,
content nvarchar(3000)
)
--首次获取游标第一个值 插入变量 @aid
fetch first from updatecursor into @aid

while(@@fetch_status=0)
begin
--print(@aid)   ;
---通过传入的 @aid ,查询其所有父级菜单信息
with tb as
(
   ---递归查询父子菜单信息   公用表表达式递归查询
   select a.*,0 as level from jkcrm.dbo.arearegion a 
   where a.areacode=@aid
   union all
   select b.* ,level+1 as level from tb a inner join jkcrm.dbo.arearegion b
   on a.parentcode=b.areacode 
)
---再将查询的表信息 拼接成字符串 此处 即采用 select 循环查询
select @pstr=@pstr+  case 
when @pstr='' then tb.areaname else ','+tb.areaname end 
from tb order by tb.level asc
 -- print(@pstr)
 --插入表变量
insert into @temptable select @aid,@pstr
---将此次通过 areacode 获取的该节点 @pstr 置空
set @pstr=''
 fetch next from updatecursor into @aid
end

--select b.areacode,b.areaname,b.parentcode,a.content,a.areacode
--from @temptable a right join dbo.arearegion b on a.areacode=b.areacode order by   b.areacode

 ---此处 即可进行 update 更新操作
 update a set a.content=b.content  from   dbo.arearegion a
 inner join @temptable b on a.areacode=b.areacode

--关闭释放游标
close updatecursor
deallocate updatecursor

原表部分脚本 sql

/*
navicat sql server data transfer

source server     : sql
source server version : 120000
source host      : .:1433
source database    : jkcrm
source schema     : dbo

target server type  : sql server
target server version : 120000
file encoding     : 65001

date: 2015-06-12 11:20:40
*/


-- ----------------------------
-- table structure for arearegion
-- ----------------------------
drop table [dbo].[arearegion]
go
create table [dbo].[arearegion] (
[areacode] varchar(10) not null ,
[areaname] varchar(50) null ,
[parentcode] varchar(10) null ,
[content] nvarchar(200) null 
)


go

-- ----------------------------
-- records of arearegion
-- ----------------------------
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110000', n'北京市', n'0', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110100', n'东城区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110200', n'西城区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110300', n'崇文区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110400', n'宣武区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110500', n'朝阳区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110600', n'丰台区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110700', n'石景山区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110800', n'海淀区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'110900', n'门头沟区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111000', n'房山区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111100', n'通州区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111200', n'顺义区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111300', n'昌平区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111400', n'大兴区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111500', n'怀柔区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111600', n'平谷区', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111700', n'密云县', n'110000', null)
go
go
insert into [dbo].[arearegion] ([areacode], [areaname], [parentcode], [content]) values (n'111800', n'延庆县', n'110000', null)