declare @县代码 as char(6)
set @县代码='530181'

select 
乡镇  =case when grouping(乡镇)=1 then ' ' else rtrim(乡镇) end ,
村委会=case when grouping(村委会)=1 then ' ' else rtrim(村委会) end ,
村小组=case when grouping(村小组)=1 then ' ' else rtrim(村小组) end ,
合计=sum(case when 使用类型 in('自留山','责任山')then 面积 else 0 end),
自留山=sum(case when 使用类型='自留山' then 面积 else 0 end),
责任山=sum(case when 使用类型='责任山' then 面积 else 0 end)
from(select 
乡镇  =(select name from code_DFT2007 where GBTCode=@县代码 and DFTCode=substring(宗地编号,7,2) +'0000'),
村委会=(select name from code_DFT2007 where GBTCode=@县代码 and DFTCode=substring(宗地编号,7,4)+'00'),
村小组=(select name from code_DFT2007 where GBTCode=@县代码 and DFTCode=substring(宗地编号,7,6)),
使用类型,面积
from 宗地申请表
where left(宗地编号,6)=@县代码
)as tmp
group by 乡镇,村委会,村小组 with cube
having not((grouping(乡镇)=1 and grouping(村委会)=0) or (grouping(村委会)=1 and grouping(村小组)=0))
order by 乡镇,村委会,村小组