你的位置:首页 > 数据库

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


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

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

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

     更新前:

    

 

     更新后:

   

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

     Code 如下:

 1 with cte(areacode,areaName,content) as 2 ( 3 --- 查询 ParentCode 为 0 (ParentCode 为 0 的为顶级菜单) 4 select areacode,areaName,cast(areaName as varchar(50)) AS content 5 from [AreaRegion] where parentcode=0 6 union all 7 -- 再进行递归查询  8 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  )
10 select * from cte

 

--- 楼主当初想法(我想复杂了 SB 了 )

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 TABLE14 (15 AreaCode INT PRIMARY KEY,16 Content NVARCHAR(3000)17 )18 --首次获取游标第一个值 插入变量 @AID19 FETCH FIRST FROM updateCursor INTO @AID20 21 WHILE(@@FETCH_STATUS=0)22 BEGIN23 --PRINT(@AID)   ;24 ---通过传入的 @AID ,查询其所有父级菜单信息25 WITH TB AS26 (27    ---递归查询父子菜单信息   公用表表达式递归查询28    SELECT A.*,0 AS LEVEL FROM JKCRM.DBO.AreaRegion A 29    WHERE A.AreaCode=@AID30    UNION ALL31    SELECT B.* ,LEVEL+1 AS LEVEL FROM TB A INNER JOIN JKCRM.DBO.AreaRegion B32    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 ASC38 -- PRINT(@pSTR)39 --插入表变量40 INSERT INTO @TempTable SELECT @aID,@pStr41 ---将此次通过 AreaCode 获取的该节点 @pStr 置空42 SET @pStr=''43  FETCH NEXT FROM updateCursor INTO @AID44 END45 46 --SELECT B.AreaCode,B.AreaName,B.ParentCode,A.Content,A.AreaCode47 --FROM @TempTable A RIGHT JOIN DBO.AreaRegion B ON A.AreaCode=B.AreaCode ORDER BY   B.AreaCode48 49 ---此处 即可进行 UPDATE 更新操作50 UPDATE A SET A.Content=B.Content  FROM   DBO.AreaRegion A51 INNER JOIN @TempTable B ON A.AreaCode=B.AreaCode52 53 --关闭释放游标54 CLOSE updateCursor55 DEALLOCATE updateCursor

 

   原表部分脚本 SQL

 

 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 Server11 Target Server Version : 12000012 File Encoding     : 6500113 14 Date: 2015-06-12 11:20:4015 */16 17 18 -- ----------------------------19 -- Table structure for AreaRegion20 -- ----------------------------21 DROP TABLE [dbo].[AreaRegion]22 GO23 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 GO32 33 -- ----------------------------34 -- Records of AreaRegion35 -- ----------------------------36 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110000', N'北京市', N'0', null)37 GO38 GO39 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110100', N'东城区', N'110000', null)40 GO41 GO42 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110200', N'西城区', N'110000', null)43 GO44 GO45 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110300', N'崇文区', N'110000', null)46 GO47 GO48 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110400', N'宣武区', N'110000', null)49 GO50 GO51 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110500', N'朝阳区', N'110000', null)52 GO53 GO54 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110600', N'丰台区', N'110000', null)55 GO56 GO57 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110700', N'石景山区', N'110000', null)58 GO59 GO60 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110800', N'海淀区', N'110000', null)61 GO62 GO63 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110900', N'门头沟区', N'110000', null)64 GO65 GO66 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111000', N'房山区', N'110000', null)67 GO68 GO69 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111100', N'通州区', N'110000', null)70 GO71 GO72 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111200', N'顺义区', N'110000', null)73 GO74 GO75 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111300', N'昌平区', N'110000', null)76 GO77 GO78 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111400', N'大兴区', N'110000', null)79 GO80 GO81 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111500', N'怀柔区', N'110000', null)82 GO83 GO84 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111600', N'平谷区', N'110000', null)85 GO86 GO87 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111700', N'密云县', N'110000', null)88 GO89 GO90 INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111800', N'延庆县', N'110000', null)

View Code