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

前端之家收集整理的这篇文章主要介绍了一道关于数据库(经典父子级 ID 关联)更新题前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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

AreaCode 、AreaName、ParentCode (原表三字段). Content __新增字段,更新该 AreaCode 下所有父级菜单信息至新增至原表的 Content 字段下面,用红线框起来(意思应该讲明白了吧.)

AreaCode:地区 ID AreaName:地区介绍 ParentCode:父级 AreaCode (Content---将该 AreaCode 下的所有父级 AreaName 拼成 类似:越城区,绍兴市,浙江省 字符串插入)

更新前:

更新后:

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

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

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

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

Code 如下:

sql;"> with cte(areacode,areaName,content) as ( --- 查询 ParentCode 为 0 (ParentCode 为 0 的为顶级菜单) select areacode,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 字段),进行联表更新。

更新脚本具体如下:

sql;"> 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

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],[Content]) VALUES (N'110100',N'东城区',N'110000',[Content]) VALUES (N'110200',N'西城区',[Content]) VALUES (N'110300',N'崇文区',[Content]) VALUES (N'110400',N'宣武区',[Content]) VALUES (N'110500',N'朝阳区',[Content]) VALUES (N'110600',N'丰台区',[Content]) VALUES (N'110700',N'石景山区',[Content]) VALUES (N'110800',N'海淀区',[Content]) VALUES (N'110900',N'门头沟区',[Content]) VALUES (N'111000',N'房山区',[Content]) VALUES (N'111100',N'通州区',[Content]) VALUES (N'111200',N'顺义区',[Content]) VALUES (N'111300',N'昌平区',[Content]) VALUES (N'111400',N'大兴区',[Content]) VALUES (N'111500',N'怀柔区',[Content]) VALUES (N'111600',N'平谷区',[Content]) VALUES (N'111700',N'密云县',[Content]) VALUES (N'111800',N'延庆县',null)

原文链接:https://www.f2er.com/mssql/63097.html

猜你在找的MsSQL相关文章