一道关于数据库(经典父子级 ID 关联)更新题

  这篇文章主要介绍了一道关于数据库(经典父子级 ID 关联)更新题,大家帮忙想想还有其它解决思路没有?

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

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

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

  更新前:

一道关于数据库(经典父子级 ID 关联)更新题


  更新后:

一道关于数据库(经典父子级 ID 关联)更新题


  在项目中,相对于此种内容,一般会存储在内存中,作为缓存使用,避免频繁连接数据库,带来的性能问题。

  代码就不上了,简单的递归即可实现。

  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 [email protected]

UNION ALL

SELECT B.* ,LEVEL+1 AS LEVEL FROM TB A INNER JOIN JKCRM.DBO.AreaRegion B

ON A.ParentCode=B.AreaCode

)

---再将查询的表信息 拼接成字符串 此处 即采用 SELECT 循环查询

SELECT @[email protected]+ 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)

分类:mssql数据库 时间:2015-03-02 人气:1
本文关键词:
分享到:

相关文章

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.035 (s). 9 q(s)