昨天，一同事發(fā)過(guò)來(lái)的一道數(shù)據(jù)庫(kù)題目，就是哪種經(jīng)典的父子級(jí) ID 在同一數(shù)據(jù)庫(kù)表中設(shè)計(jì)類型。需要在原表中添加一個(gè)字段，同時(shí)，將該節(jié)點(diǎn)的父子級(jí)詳細(xì)信息插入到原表新增的一字段中，具體效果如下圖。
AreaCode 、AreaName、ParentCode （原表三字段）. Content __新增字段，更新該 AreaCode 下所有父級(jí)菜單信息至新增至原表的 Content 字段下面，用紅線框起來(lái)（意思應(yīng)該講明白了吧.）
AreaCode：地區(qū) ID AreaName：地區(qū)介紹 ParentCode：父級(jí) AreaCode （Content---將該 AreaCode 下的所有父級(jí) AreaName 拼成 類似：越城區(qū),紹興市,浙江省 字符串插入）
更新前:
更新后:
在項(xiàng)目中，相對(duì)于此種內(nèi)容，一般會(huì)存儲(chǔ)在內(nèi)存中，作為緩存使用，避免頻繁連接數(shù)據(jù)庫(kù)，帶來(lái)的性能問(wèn)題。
代碼就不上了，簡(jiǎn)單的遞歸即可實(shí)現(xiàn)。
1 先從數(shù)據(jù)庫(kù)將該表所有內(nèi)容取出來(lái)，緩存起來(lái)。（該表差不多 3000 左右條數(shù)據(jù)）
2 再寫個(gè)遞歸函數(shù)，根據(jù)每次傳入的 AreaCode（第一步已緩存數(shù)據(jù)庫(kù)取出來(lái)的整表集合 可轉(zhuǎn)成 Dictionary<int,AreaRegion> 類型），獲取其 ParentCode 值，只要 ParentCode 不為 0（即不為頂級(jí)結(jié)點(diǎn)，ParentCode 為頂級(jí)結(jié)點(diǎn)），那么，繼續(xù)調(diào)用該函數(shù)，傳入本次查詢實(shí)體的 ParentCode 信息。
3 在代碼很方便查出父結(jié)點(diǎn)所有信息，再 Update 至數(shù)據(jù)庫(kù)即可。
感謝 清海揚(yáng)波 grayboy 完全可以通過(guò)公用表表達(dá)式完成
Code 如下：
with cte(areacode,areaName,content) as(--- 查詢 ParentCode 為 0 （ParentCode 為 0 的為頂級(jí)菜單）select areacode,areaName,cast(areaName as varchar(50)) AS contentfrom [AreaRegion] where parentcode=0union all-- 再進(jìn)行遞歸查詢 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
--- 樓主當(dāng)初想法（我想復(fù)雜了 SB 了 ）
1 樓主，首當(dāng)其沖想到的是，即采用游標(biāo)存儲(chǔ)整表記錄。
2 游標(biāo)每次取得一條記錄的 AreaCode ，再通過(guò)該 AreaCode 查出其所有父級(jí)表（包括自身）信息，最后，將查詢出的該表某字段拼成字符串，
插入表變量中（兩字段分別為：AreaCode、Content）。
3 最后，將定義的表變量與原表通過(guò) INNER JOIN（表變量中插入了 AreaCode 字段），進(jìn)行聯(lián)表更新。
更新腳本具體如下：
USE JKCRMGO--定義游標(biāo)DECLARE updateCursor CURSOR SCROLL FORSELECT A.AreaCode FROM DBO.AreaRegion A--打開(kāi)游標(biāo)OPEN updateCursor--定義變量 存儲(chǔ)依次獲取游標(biāo)值DECLARE @aID NVARCHAR(30)=''---定義變量 存儲(chǔ)父結(jié)點(diǎn)具體信息DECLARE @pStr NVARCHAR(300)='';--定義表變量 存儲(chǔ)兩字段 AreaCode Content ( Content :為該字段所有父級(jí)菜單信息)DECLARE @TempTable TABLE(AreaCode INT PRIMARY KEY,Content NVARCHAR(3000))--首次獲取游標(biāo)第一個(gè)值 插入變量 @AIDFETCH FIRST FROM updateCursor INTO @AIDWHILE(@@FETCH_STATUS=0)BEGIN--PRINT(@AID) ;---通過(guò)傳入的 @AID ,查詢其所有父級(jí)菜單信息WITH TB AS( ---遞歸查詢父子菜單信息 公用表表達(dá)式遞歸查詢 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 循環(huán)查詢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---將此次通過(guò) AreaCode 獲取的該節(jié)點(diǎn) @pStr 置空SET @pStr='' FETCH NEXT FROM updateCursor INTO @AIDEND--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 ---此處 即可進(jìn)行 UPDATE 更新操作 UPDATE A SET A.Content=B.Content FROM DBO.AreaRegion A INNER JOIN @TempTable B ON A.AreaCode=B.AreaCode--關(guān)閉釋放游標(biāo)CLOSE updateCursorDEALLOCATE updateCursor
原表部分腳本 SQL
/*Navicat SQL Server Data TransferSource Server : SQLSource Server Version : 120000Source Host : .:1433Source Database : JKCRMSource Schema : dboTarget Server Type : SQL ServerTarget Server Version : 120000File Encoding : 65001Date: 2015-06-12 11:20:40*/-- ------------------------------ Table structure for AreaRegion-- ----------------------------DROP TABLE [dbo].[AreaRegion]GOCREATE 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)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110100', N'東城區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110200', N'西城區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110300', N'崇文區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110400', N'宣武區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110500', N'朝陽(yáng)區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110600', N'豐臺(tái)區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110700', N'石景山區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110800', N'海淀區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110900', N'門頭溝區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111000', N'房山區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111100', N'通州區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111200', N'順義區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111300', N'昌平區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111400', N'大興區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111500', N'懷柔區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111600', N'平谷區(qū)', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111700', N'密云縣', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111800', N'延慶縣', N'110000', null)
新聞熱點(diǎn)
疑難解答
圖片精選
