With as 递归查询

use TEST
create table Provinces
(
    pro_Id int primary key identity(1,1),
    pro_Name nvarchar(255),
    pro_Code nvarchar(8),
    pro_PId int
) 
exec sp_rename 'Provinces', 'Area'
select * from Area 
execute sp_rename 'Area.pro_Id','a_Id','Column'

insert into Area values('河南省','0023',0)
insert into Area(a_Name,a_Code,a_PId) values('郑州市','0024',1)
insert into Area values('金水区','0025',2)
insert into Area values('北京市','0021',0)
insert into Area(a_Name,a_Code,a_PId) values('朝阳区','0022',4) 

--若
declare @count int;--; 必须的

--公共表表达式
--1:
/*
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 
) 
*/
--2:

with CTE1
as
(
    select a_Id from Area where a_Code='0021'
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 ;