转载

一道关于数据库(经典父子级 ID 关联)更新题,大家帮忙想想还有其它解决思路没有?

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

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

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

 更新前:

一道关于数据库(经典父子级 ID 关联)更新题,大家帮忙想想还有其它解决思路没有?

 更新后:

一道关于数据库(经典父子级 ID 关联)更新题,大家帮忙想想还有其它解决思路没有?

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

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

插入表变量中(两字段分别为:AreaCode、Content)。

3  最后,将定义的表变量与原表通过 INNER JOIN(表变量中插入了 AreaCode 字段),进行联表更新。

更新脚本具体如下:

 1 USE JKCRM  2 GO  3 --定义游标  4 DECLARE  updateCursor CURSOR SCROLL FOR  5 SELECT A.AreaCode   FROM DBO.AreaRegion A  6 --打开游标  7 OPEN updateCursor  8 --定义变量 存储依次获取游标值  9 DECLARE @aID NVARCHAR(30)='' 10 ---定义变量  存储父结点具体信息 11 DECLARE @pStr NVARCHAR(300)=''; 12 --定义表变量 存储两字段 AreaCode  Content ( Content :为该字段所有父级菜单信息) 13 DECLARE @TempTable TABLE 14 ( 15 AreaCode INT PRIMARY KEY, 16 Content NVARCHAR(3000) 17 ) 18 --首次获取游标第一个值 插入变量 @AID 19 FETCH FIRST FROM updateCursor INTO @AID 20  21 WHILE(@@FETCH_STATUS=0) 22 BEGIN 23 --PRINT(@AID)      ; 24 ---通过传入的 @AID ,查询其所有父级菜单信息 25 WITH TB AS 26 ( 27       ---递归查询父子菜单信息      公用表表达式递归查询 28       SELECT A.*,0 AS LEVEL  FROM JKCRM.DBO.AreaRegion A  29       WHERE A.AreaCode=@AID 30       UNION ALL 31       SELECT B.* ,LEVEL+1 AS LEVEL  FROM  TB A INNER JOIN  JKCRM.DBO.AreaRegion B 32       ON A.ParentCode=B.AreaCode  33 ) 34 ---再将查询的表信息 拼接成字符串  此处 即采用 SELECT 循环查询 35 SELECT @pStr=@pStr+   CASE   36 WHEN @pStr='' THEN TB.AreaName ELSE ','+TB.AreaName  END  37 FROM TB  ORDER BY TB.LEVEL ASC 38  -- PRINT(@pSTR) 39  --插入表变量 40 INSERT INTO @TempTable SELECT @aID,@pStr 41 ---将此次通过 AreaCode 获取的该节点 @pStr 置空 42 SET @pStr='' 43  FETCH NEXT FROM updateCursor INTO @AID 44 END 45  46 --SELECT B.AreaCode,B.AreaName,B.ParentCode,A.Content,A.AreaCode 47 --FROM @TempTable  A RIGHT JOIN DBO.AreaRegion B ON A.AreaCode=B.AreaCode  ORDER BY     B.AreaCode 48  49  ---此处 即可进行 UPDATE 更新操作 50  UPDATE A  SET  A.Content=B.Content    FROM      DBO.AreaRegion A 51  INNER JOIN @TempTable B ON A.AreaCode=B.AreaCode 52  53 --关闭释放游标 54 CLOSE updateCursor 55 DEALLOCATE updateCursor

原表部分脚本 SQL

一道关于数据库(经典父子级 ID 关联)更新题,大家帮忙想想还有其它解决思路没有?
 1 /*  2 Navicat SQL Server Data Transfer  3   4 Source Server         : SQL  5 Source Server Version : 120000  6 Source Host           : .:1433  7 Source Database       : JKCRM  8 Source Schema         : dbo  9  10 Target Server Type    : SQL Server 11 Target Server Version : 120000 12 File Encoding         : 65001 13  14 Date: 2015-06-12 11:20:40 15 */ 16  17  18 -- ---------------------------- 19 -- Table structure for AreaRegion 20 -- ---------------------------- 21 DROP TABLE [dbo].[AreaRegion] 22 GO 23 CREATE TABLE [dbo].[AreaRegion] ( 24 [AreaCode] varchar(10) NOT NULL , 25 [AreaName] varchar(50) NULL , 26 [ParentCode] varchar(10) NULL , 27 [Content] nvarchar(200) NULL  28 ) 29  30  31 GO 32  33 -- ---------------------------- 34 -- Records of AreaRegion 35 -- ---------------------------- 36 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110000', N'北京市', N'0', null) 37 GO 38 GO 39 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110100', N'东城区', N'110000', null) 40 GO 41 GO 42 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110200', N'西城区', N'110000', null) 43 GO 44 GO 45 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110300', N'崇文区', N'110000', null) 46 GO 47 GO 48 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110400', N'宣武区', N'110000', null) 49 GO 50 GO 51 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110500', N'朝阳区', N'110000', null) 52 GO 53 GO 54 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110600', N'丰台区', N'110000', null) 55 GO 56 GO 57 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110700', N'石景山区', N'110000', null) 58 GO 59 GO 60 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110800', N'海淀区', N'110000', null) 61 GO 62 GO 63 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110900', N'门头沟区', N'110000', null) 64 GO 65 GO 66 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111000', N'房山区', N'110000', null) 67 GO 68 GO 69 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111100', N'通州区', N'110000', null) 70 GO 71 GO 72 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111200', N'顺义区', N'110000', null) 73 GO 74 GO 75 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111300', N'昌平区', N'110000', null) 76 GO 77 GO 78 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111400', N'大兴区', N'110000', null) 79 GO 80 GO 81 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111500', N'怀柔区', N'110000', null) 82 GO 83 GO 84 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111600', N'平谷区', N'110000', null) 85 GO 86 GO 87 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111700', N'密云县', N'110000', null) 88 GO 89 GO 90 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111800', N'延庆县', N'110000', null)
View Code
正文到此结束
Loading...