采用路径标示扁平化树设计方式

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Property](
 [PropertyId] [int] IDENTITY(1,1) NOT NULL,
 [PropertyNo] [varchar](10) NOT NULL,
 [PropertyName] [varchar](50) NOT NULL,
 [PropertyCName] [varchar](50) NULL,
 [PropertyEName] [varchar](50) NULL,
 [Pinyin] [varchar](100) NULL,
 [PinyinAbb] [varchar](50) NULL,
 [ENameAbb] [varchar](50) NULL,
 [PropertyPath] [varchar](10) NULL,
 [ParentPropertyPath] [varchar](10) NULL,
 [PropertyLevel] [smallint] NULL,
 [SeqNo] [smallint] NULL,
 [Remark] [varchar](200) NULL,
 [IsValid] [bit] NULL,
 [Company] [smallint] NULL,
 [LastModifyMan] [varchar](50) NULL,
 [LastModifyTime] [datetime] NULL,
 CONSTRAINT [PK_Property] PRIMARY KEY CLUSTERED
(
 [PropertyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性代号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性繁体名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyCName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性英文名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyEName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称拼音' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'Pinyin'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称拼音缩写' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PinyinAbb'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'英文名缩写' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'ENameAbb'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyPath'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父级属性路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'ParentPropertyPath'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性层级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'PropertyLevel'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'SeqNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否有效' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'IsValid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'Company'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'LastModifyMan'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property', @level2type=N'COLUMN',@level2name=N'LastModifyTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Property'
GO

ALTER TABLE [dbo].[Property] ADD  CONSTRAINT [DF_Property_SortNo]  DEFAULT ((0)) FOR [SeqNo]
GO

ALTER TABLE [dbo].[Property] ADD  CONSTRAINT [DF_Property_Company]  DEFAULT ((0)) FOR [Company]
GO

 

 

树的路径设计方式

好处：

找当前及子LIKE '5%'

打所有的子：LIKE '5.%'