if exists (select * from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01')begindeclare @mianji char(10),@灌木 char(10),@疏林 char(10),@红树林 char(10),@竹林 char(10),@混交林 char(10),@纯林 char(10),@乔木林 char(10),@有林地 char(10)if exists (select * from xbyz where xbyz.dilei = '1300' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @灌木 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @灌木 = '0'if exists (select * from xbyz where xbyz.dilei = '1200' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @疏林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @疏林 = '0'if exists (select * from xbyz where xbyz.dilei = '1120' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @红树林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @红树林 = '0'if exists (select * from xbyz where xbyz.dilei = '1130' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @竹林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @竹林 = '0'if exists (select * from xbyz where xbyz.dilei = '1112' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @混交林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @混交林 = '0'if exists (select * from xbyz where xbyz.dilei = '1111' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @纯林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @纯林 = '0'set @乔木林 = convert(int,@纯林) +convert(int, @混交林)
--有林地小计 = 乔木林小计 + 竹林 + 红树林
set @有林地 = convert(int,@乔木林 )+ convert(int,@竹林 )+convert(int, @红树林)select @mianji =sum(xbyz.mj) from xbyz
where xbyz.sllb = '1' and xbyz.lbh = '01'insert into tdtj1( 统计单位,森林类别,总面积,林地合计,灌木林,疏林地,红树林,竹林,混交林,纯林,乔木林小计,有林地小计)
values ('01','公益林',@mianji,@mianji,@灌木,@疏林,@红树林,@竹林,@混交林,@纯林,@乔木林,@有林地) end
begin
select @灌木 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @灌木 = '0'if exists (select * from xbyz where xbyz.dilei = '1200' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @疏林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @疏林 = '0'if exists (select * from xbyz where xbyz.dilei = '1120' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @红树林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @红树林 = '0'if exists (select * from xbyz where xbyz.dilei = '1130' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @竹林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @竹林 = '0'if exists (select * from xbyz where xbyz.dilei = '1112' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @混交林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @混交林 = '0'if exists (select * from xbyz where xbyz.dilei = '1111' and xbyz.sllb = '1' and xbyz.lbh = '01' )
begin
select @纯林 = xbyz.mj from xbyz where xbyz.sllb = '1' and xbyz.lbh = '01'
group by xbyz.mj
end
else
set @纯林 = '0'set @乔木林 = convert(int,@纯林) +convert(int, @混交林)
--有林地小计 = 乔木林小计 + 竹林 + 红树林
set @有林地 = convert(int,@乔木林 )+ convert(int,@竹林 )+convert(int, @红树林)select @mianji =sum(xbyz.mj) from xbyz
where xbyz.sllb = '1' and xbyz.lbh = '01'insert into tdtj1( 统计单位,森林类别,总面积,林地合计,灌木林,疏林地,红树林,竹林,混交林,纯林,乔木林小计,有林地小计)
values ('01','公益林',@mianji,@mianji,@灌木,@疏林,@红树林,@竹林,@混交林,@纯林,@乔木林,@有林地) end
@纯林 money,@乔木林 money,@有林地 moneyselect @灌木=sum(case dilei when '1300' then mj else '0' end),
@疏林=sum(case dilei when '1200' then mj else '0' end),
@红树林=sum(case dilei when '1120' then mj else '0' end),
@竹林=sum(case dilei when '1130' then mj else '0' end),
@混交林=sum(case dilei when '1112' then mj else '0' end),
@纯林=sum(case dilei when '1111' then mj else '0' end),
@mianji=sum(mj)
from xbyz
where xbyz.sllb = '1' and xbyz.lbh = '01'set @乔木林 = @纯林+@混交林
--有林地小计 = 乔木林小计 + 竹林 + 红树林
set @有林地 = @乔木林 + @竹林 +@红树林insert into tdtj1( 统计单位,森林类别,总面积,林地合计,灌木林,疏林地,红树林,竹林,混交林,纯林,乔木林小计,有林地小计)
values ('01','公益林',@mianji,@mianji,@灌木,@疏林,@红树林,@竹林,@混交林,@纯林,@乔木林,@有林地)
insert into tdtj1( 统计单位,森林类别,总面积,林地合计,灌木林,疏林地,红树林,竹林,混交林,纯林,乔木林小计,有林地小计)
select '01','公益林',sum(mj),sum(mj),
sum(case dilei when '1300' then mj else 0 end),
sum(case dilei when '1200' then mj else 0 end),
sum(case dilei when '1120' then mj else 0 end),
sum(case dilei when '1130' then mj else 0 end),
sum(case dilei when '1112' then mj else 0 end),
sum(case dilei when '1111' then mj else 0 end),
sum(case when dilei='1112' or dilei='1111' then mj else 0 end),
sum(case when dilei='1112' or dilei='1111' or dilei='1120' or or dilei='1130' then mj else 0 end),
@mianji=sum(mj)
from xbyz
where xbyz.sllb = '1' and xbyz.lbh = '01'
select lbh,sllb,sum(mj),sum(mj),
sum(case dilei when '1300' then mj else '0' end),
sum(case dilei when '1200' then mj else '0' end),
sum(case dilei when '1120' then mj else '0' end),
sum(case dilei when '1130' then mj else '0' end),
sum(case dilei when '1112' then mj else '0' end),
sum(case dilei when '1111' then mj else '0' end),
sum(case when dilei='1112' or dilei='1111' then mj else '0' end),
sum(case when dilei='1112' or dilei='1111' or dilei='1120' or dilei='1130' then mj else '0' end)
from xbyz
group by lbh,sllb