A question about database (Classic parent-child ID Association), parent-child id

A question about database (Classic parent-child ID Association), parent-child id

Yesterday, a database question that happened together was the type of classic parent-child IDs designed in the same database table. You need to add a field to the original table and insert the Parent-Child details of the node to the new field in the original table. The specific effect is shown in.

AreaCode, AreaName, and ParentCode (three fields in the original table ). content _ add a field, update all the parent menu information under the AreaCode to the Content field of the new table, and use the red line to box it (meaning it should be clear .)

AreaCode: Region ID AreaName: region Introduction ParentCode: parent-level AreaCode (Content --- splice all parent-level AreaName under this AreaCode into a string similar to: yuecheng district, Shaoxing City, and Zhejiang Province)

Before update:

After update:

In projects, such content is generally stored in the memory and used as a cache to avoid frequent database connection and performance problems.

The code will not be available, and simple recursion can be implemented.

1. obtain all the table content from the database and cache it. (About 3000 data records in this table)
2. Write a recursive function and obtain its ParentCode value based on the input AreaCode (the entire table set retrieved from the cache database in the first step can be converted to the Dictionary <int, AreaRegion> type, as long as the ParentCode is not 0 (that is, the ParentCode is not a top-level node and the ParentCode is a top-level node), continue to call this function and pass in the ParentCode information of the object for this query.
3. You can easily find all the information of the parent node in the code, and then Update it to the database.

Thanks to the grayboy function, you can use a common table expression.

The Code is as follows:

With cte (areacode, areaName, content) as (--- query top-level menus with ParentCode 0) select areacode, areaName, cast (areaName as varchar (50 )) AS contentfrom [AreaRegion] where parentcode = 0 union all -- then perform recursive query select. areacode,. areaName, cast (. content + ',' + B. areaName as varchar (50) AS content 9 from [AreaRegion] a 10 inner join cte B on. parentcode = B. areacode) select * from cte

--- The original idea of the landlord (I think it's complicated)

1. The first thing that comes to mind is using a cursor to store the whole table records.

2. Each time the cursor acquires the AreaCode of a record, it uses the AreaCode to find information about all its parent tables (including its own). Finally, it concatenates a field in the queried table into a string,

Insert table variables (the two fields are AreaCode and Content ).

3. Finally, the defined table variables and the original table are updated through inner join (the AreaCode field is inserted in the table variables.

The update script is as follows:

Use jkcrmgo -- Define the cursor declare updateCursor cursor scroll forselect. areaCode from dbo. areaRegion A -- OPEN the cursor OPEN updateCursor -- Define the variable storage to get the cursor value in turn DECLARE @ aID NVARCHAR (30) = ''--- define the variable storage parent node information DECLARE @ pStr NVARCHAR (300) = ''; -- defines the TABLE variable to store the two fields AreaCode Content (Content: For all the parent menu information of this field) DECLARE @ TempTable TABLE (AreaCode int primary key, content NVARCHAR (3000) -- get the FIRST value of the cursor For The FIRST Time Insert the variable @ aidfetch first from updateCursor INTO @ AIDWHILE (@ FETCH_STATUS = 0) BEGIN--PRINT (@ AID ); --- query all parent menu information with tb as (--- recursive query of parent and child menu information public table expression recursive query of select. *, 0 as level from jkcrm. DBO. areaRegion a where. areaCode = @ aid union all select B. *, LEVEL + 1 as level from tb a inner join jkcrm. DBO. areaRegion B ON. parentCode = B. areaCode) --- concatenate the queried table information into a string. Here, SELECT @ pStr = @ pStr + case when @ pStr = ''then tb is queried cyclically. areaName ELSE ',' + TB. areaName end from tb order by tb. level asc -- PRINT (@ pSTR) -- INSERT the table variable insert into @ TempTable SELECT @ aID, @ pStr --- SET @ pStr = ''fetch next from updateCursor INTO @ AIDEND--SELECT B. areaCode, B. areaName, B. parentCode,. content,. areaCode -- FROM @ TempTable a right join dbo. areaRegion B ON. areaCode = B. areaCode order by B. areaCode --- here you can perform the UPDATE operation update a set. content = B. content from dbo. areaRegion a inner join @ TempTable B ON. areaCode = B. areaCode -- CLOSE release cursor CLOSE updateCursorDEALLOCATE updateCursor

Some script SQL statements of the original table

/* Navicat SQL Server Data TransferSource Server: SQLSource Server Version: 120000 Source Host :.: 1433 Source Database: JKCRMSource Schema: dboTarget Server Type: SQL ServerTarget Server Version: 120000 File Encoding: 65001 Date: 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 '000000', N 'beijing', N '0', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'dongcheng district ', N '000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'xicheng district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'chongwen region', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'xuanwu district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'chaoyang district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'fengtai district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'shijingshan district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'haidian district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'mentougou district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'fangshan district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'tongzhou district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'shunyi district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'changping district ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'daxing region', N '000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N '000000', N 'huairou region', N '000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'grain partition ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'miyun County ', n'000000', null) gogoinsert into [dbo]. [AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (n'000000', n'yanqing County ', n'000000', null)