--第一级 
select ciid from communityinfor as Community where parent!=0 and parent not in (select ciid from communityinfor )
--第二级
select ciid from communityinfor where parent in (select ciid from communityinfor as Community where parent!=0 and parent not in (select ciid from communityinfor ))
--第三级
select ciid from communityinfor where parent in(select ciid from communityinfor where parent in (select ciid from communityinfor as Community where parent!=0 and parent not in (select ciid from communityinfor )))

/*==============================================================*/
/* Table: communityinfor                                        */
/*==============================================================*/
create table communityinfor (
   ciid                 int                  identity,
   levelid              int                  not null,
   parent               int                  not null,
   communityname        varchar(50)          not null,
   constraint pk_communityinfor primary key (ciid)
)
go

declare @currentuser sysname
select @currentuser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '小区信息
   需要具体到室
   页面需要树控件显示出小区的所有信息。 从小区到室。 所有节点的信息
   1.CIID ID
   2.LevelID    级别信息外键ID[LevelInfor]表的外键。指示该节点所属的级别
   
   4.Parent      父级。例如 1单元，它所属的栋级 是哪个 ？此字段引用自己的主键
   5.CommunityName      节点的完整名称。  例如一栋一单元， 或者一栋一单元0101室
   ',
   'user', @currentuser, 'table', 'communityinfor'
go