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

站长资源 2024/11/23 佚名

43 1538 43

DDR爱好者之家 Design By 杰米

昨天，一同事发过来的一道数据库题目，就是哪种经典的父子级 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)

父子级,ID,关联

DDR爱好者之家 Design By 杰米

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

DDR爱好者之家 Design By 杰米

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

暂无评论...

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

SQL Server中将查询结果转换为Json格式脚本分享

Sql Server中的DBCC命令详细介绍

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

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

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

SQL Server中将查询结果转换为Json格式脚本分享

Sql Server中的DBCC命令详细介绍

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

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存