use TEST

create table Provinces

(

    pro_Id int primary key identity(,),

    pro_Name nvarchar(),

    pro_Code nvarchar(),

    pro_PId int

)

exec sp_rename 'Provinces', 'Area'

select * from Area

execute sp_rename 'Area.pro_Id','a_Id','Column'

insert into Area values('河南省','',)

insert into Area(a_Name,a_Code,a_PId) values('郑州市','',)

insert into Area values('金水区','',)

insert into Area values('北京市','',)

insert into Area(a_Name,a_Code,a_PId) values('朝阳区','',) 

--若

declare @count int;--; 必须的

--公共表表达式

--:

/*

with

CTE1(id) AS

(

--查询出当前省(父)

SELECT a_Id FROM Area where a_Code='0023'

  union all

--显示当前级别以下的所有有关的数据(子)

select Area.a_Id from CTE1        --查找出属于当前省的数据

    inner join Area on CTE1.id=Area.a_PId --递归

  ),

CTE2 as

(    --总计

    select count(*) as cou  from CTE1

)

*/

--:

with CTE1

as

(

    select a_Id from Area where a_Code=''

union all

    select Area.a_Id from CTE1

inner join Area on CTE1.a_Id=Area.a_PId

)

--

select * from Area where a_Id in( select * from CTE1)

union

select null,null,'总计', cou from CTE2;

--则

print  @count ;