select c.acc_no as 单位编号,c.acc_name as 单位名称,count(c.acc_no) as 帐户数量,c.unit_level
into #1
from(
select a.top_acc_no as acc_no,b.unit_name+'(一级及以下单位总计)' as acc_name,b.unit_level,b.p_unit_no,b.top_unit_no
from t_acc a,t_unit b
where a.top_acc_no =b.unit_no
union all
select a.p_acc_no as acc_no,(b.unit_name+case b.unit_level when '2' then '(二级以下单位总计)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no
from t_acc a,t_unit b
where a.p_acc_no =b.unit_no and b.unit_level='2'
union all
select b.unit_no as acc_no,(b.unit_name+case b.unit_level when '2' then '(二级以下单位总计)' end) as acc_name,b.unit_level,b.p_unit_no,b.top_unit_no
from t_acc a,t_unit b
where a.acc_level>3 and a.top_acc_no=b.top_unit_no and b.unit_level='2'
union all
select a.acc_no,(a.acc_name+case a.acc_level when '2' then '(二级单位)' when '3' then '(三级单位数量)' else '(四级单位数量)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no
from t_acc a,t_unit b
where a.acc_no =b.unit_no
) c
group by c.acc_no,c.acc_name,c.unit_level
select identity(int,1,1)as #_ID,* into #2 from #1 where unit_level='1'
select #_id as 编号,单位编号,单位名称,帐户数量 from(
select * from #2
union all
select null as #_ID,* from #1 where unit_level<>1
) c
order by case len(c.单位编号) when 3 then c.单位编号 else substring(c.单位编号,3,len(c.单位编号)-2)end