一道關於資料庫(經典父子級 ID 關聯)更新題

 　　這篇文章主要介紹了一道關於資料庫(經典父子級 ID 關聯)更新題，大家幫忙想想還有其它解決思路沒有?

　　昨天，一同事發過來的一道資料庫題目，就是哪種經典的父子級 ID 在同一資料庫表中設計類型。需要在原表中添加一個欄位，同時，將該節點的父子級詳細資料插入到原表新增的一欄位中，具體效果如下圖。

　　AreaCode 、AreaName、ParentCode (原表三欄位). Content __新增欄位，更新該 AreaCode 下所有父級菜單資訊至新增至原表的 Content 欄位下面，用紅線框起來(意思應該講明白了吧.)

　　AreaCode：地區 ID AreaName：地區介紹 ParentCode：父級 AreaCode (Content---將該 AreaCode 下的所有父級 AreaName 拼成 類似：越城區,紹興市,浙江省 字串插入)

　　更新前:

　　更新後:

　　在項目中，相對於此種內容，一般會儲存在記憶體中，作為緩衝使用，避免頻繁串連資料庫，帶來的效能問題。

　　代碼就不上了，簡單的遞迴即可實現。

　　1 先從資料庫將該表所有內容取出來，緩衝起來。(該表差不多 3000 左右條資料)

　　2 再寫個遞迴函式，根據每次傳入的 AreaCode(第一步已快取資料庫取出來的整表集合 可轉成 Dictionary

　　3 在代碼很方便查出父結點所有資訊，再 Update 至資料庫即可。

　　感謝 清海揚波 grayboy 完全可以通過通用資料表運算式完成

　　Code 如下：

　　?

1 2 3 4 5 6 7 8 9

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 欄位)，進行聯表更新。

　　更新指令碼具體如下：

　　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

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

　　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

/* 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)