SQL Server 2008中原生的分层数据类型:hierarchyid

SQL Server 2008中原生的分层数据类型:hierarchyid

如果是在SQL 2000中,我们需要读取分层结构数据时,不得不借助递归。在SQL server 2005中,我们可以使用CTE,当然,好的数据结构设计可以避免你使用CTE,请看这里:http://blog.csdn.net/downmoon/archive/2009/10/23/4715814.aspx

如果你是一个数据库设计新手,那么在sql server 2008中可以使用新的原生分层结构数据hierarchyid。

关于它的详细说明,请看MSDN:

http://msdn.microsoft.com/zh-cn/library/bb677173%28v=sql.100%29.aspx

http://64.4.11.252/zh-cn/library/bb677173.aspx

http://msdn.microsoft.com/en-us/magazine/cc794278.aspx#id0090037

需要注意的是,它可以改造传统数据表而成。但不可以实现与XML互相转换。

下面我们看一个示例:

[ruby] view plaincopyprint?

  1. if OBJECT_ID('[Category_hierarchyid]') is not null

  2. drop table [Category_hierarchyid]

  3. go

  4. –创建表

  5. CREATE TABLE [Category_hierarchyid]

  6. (

  7. H_ID hierarchyid NOT NULL,

  8. C_ID INT primary key identity(1000,1) NOT NULL,

  9. C_Name NVARCHAR(50) NOT NULL,

  10. Title NVARCHAR(50) NOT NULL

  11. )

  12. GO

  13. TRUNCATE table [Category_hierarchyid]

  14. GO

  15. INSERT INTO [Category_hierarchyid]([H_ID],[C_Name],[Title])

  16. select '/','蔬菜','蔬菜' union all

  17. select '/1/','根菜类','蔬菜' union all

  18. select '/2/','叶菜类','蔬菜' union all

  19. select '/3/','茄果类','蔬菜' union all

  20. select '/4/','甘蓝类','蔬菜' union all

  21. select '/1/1/','萝卜','蔬菜' union all

  22. select '/1/1/','胡萝卜','蔬菜' union all

  23. select '/4/2/','甘蓝','蔬菜' union all

  24. select '/2/4/','花椰菜','蔬菜' union all

  25. select '/3/3/','茄子','蔬菜' union all

  26. select '/3/3/','番茄','蔬菜' union ALL

  27. select '/1/1/5/','白萝卜','蔬菜'

  28. go

查询示例:

[ruby] view plaincopyprint?

  1. /***********SQL server 2008 中的原生分层数据:hierarchyid 3w@live.cn*****************************/

  2. –查看所有的分类

  3. SELECT * FROM [Category_hierarchyid]

  4. /*

  5. H_ID C_ID C_Name Title

  6. 0x 1000 蔬菜 蔬菜

  7. 0x58 1001 根菜类 蔬菜

  8. 0x68 1002 叶菜类 蔬菜

  9. 0x78 1003 茄果类 蔬菜

  10. 0x84 1004 甘蓝类 蔬菜

  11. 0x5AC0 1005 萝卜 蔬菜

  12. 0x5AC0 1006 胡萝卜 蔬菜

  13. 0x85A0 1007 甘蓝 蔬菜

  14. 0x6C20 1008 花椰菜 蔬菜

  15. 0x7BC0 1009 茄子 蔬菜

  16. 0x7BC0 1010 番茄 蔬菜

  17. 0x5AE3 1011 白萝卜 蔬菜

  18. */

  19. –查看所有的分类及级别

  20. SELECT *,H_ID.GetLevel() AS Level FROM [Category_hierarchyid]

  21. /*

  22. H_ID C_ID C_Name Title Level

  23. 0x 1000 蔬菜 蔬菜 0

  24. 0x58 1001 根菜类 蔬菜 1

  25. 0x68 1002 叶菜类 蔬菜 1

  26. 0x78 1003 茄果类 蔬菜 1

  27. 0x84 1004 甘蓝类 蔬菜 1

  28. 0x5AC0 1005 萝卜 蔬菜 2

  29. 0x5AC0 1006 胡萝卜 蔬菜 2

  30. 0x85A0 1007 甘蓝 蔬菜 2

  31. 0x6C20 1008 花椰菜 蔬菜 2

  32. 0x7BC0 1009 茄子 蔬菜 2

  33. 0x7BC0 1010 番茄 蔬菜 2

  34. 0x5AE3 1011 白萝卜 蔬菜 3

  35. */

  36. –查看根菜类及其子分类

  37. DECLARE @Parent hierarchyid

  38. SELECT @Parent=H_ID FROM [Category_hierarchyid] WHERE C_ID=1001

  39. —-PRINT @Parent.ToString()

  40. SELECT *,H_ID.GetLevel() AS Level FROM [Category_hierarchyid]

  41. WHERE H_ID.IsDescendantOf(@Parent)=1

  42. /*

  43. H_ID C_ID C_Name Title Level

  44. 0x58 1001 根菜类 蔬菜 1

  45. 0x5AC0 1005 萝卜 蔬菜 2

  46. 0x5AC0 1006 胡萝卜 蔬菜 2

  47. 0x5AE3 1011 白萝卜 蔬菜 3

  48. */

  49. –查看胡萝卜及其所有父分类

  50. DECLARE @Son hierarchyid

  51. SELECT @Son=H_ID FROM [Category_hierarchyid] WHERE C_ID=1006

  52. SELECT *,H_ID.GetLevel()AS Level FROM [Category_hierarchyid]

  53. WHERE @Son.IsDescendantOf(H_ID)=1

  54. /*

  55. H_ID C_ID C_Name Title Level

  56. 0x 1000 蔬菜 蔬菜 0

  57. 0x58 1001 根菜类 蔬菜 1

  58. 0x5AC0 1005 萝卜 蔬菜 2

  59. 0x5AC0 1006 胡萝卜 蔬菜 2

  60. */

相关资源:

1、SQL2005/2008中的CTE应用–递归查询

http://blog.csdn.net/downmoon/archive/2009/10/23/4715814.aspx

2、gridview中分组数据的显示

http://blog.csdn.net/downmoon/archive/2008/08/26/2832007.aspx

3、DataRelation(DataSet中父子表)的一个示例

http://blog.csdn.net/downmoon/archive/2009/12/27/5085410.aspx

此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。