日期:2014-05-17 浏览次数:20501 次
create table u01
(单位代码 varchar(10), 管护方式 int, 相关林班号 varchar(10), 面积 int)
insert into u01
select '120101', 2, '2', 1 union all
select '120102', 1, '1,3', 2 union all
select '120103', 3, '1,2,4', 3 union all
select '120102', 1, '2,3', 4 union all
select '120102', 2, '1,4', 5
select substring(a.单位代码,3,2) '林业局',
substring(a.单位代码,5,2) '林场',
(select count(1)
from
(select 相关林班号 from u01 b
where substring(b.单位代码,3,2)=substring(a.单位代码,3,2)
and substring(b.单位代码,5,2)=substring(a.单位代码,5,2)) c
inner join master.dbo.spt_values d
on d.type='P' and d.number between 1 and len(c.相关林班号)
and substring(','+c.相关林班号,d.number,1)=',') '林班个数',
(select sum(面积) from u01 b
where substring(b.单位代码,3,2)=substring(a.单位代码,3,2)
and substring(b.单位代码,5,2)=substring(a.单位代码,5,2)
and b.管护方式=1) '方式1面积',
(select sum(面积) from u01 b
where substring(b.单位代码,3,2)=substring(a.单位代码,3,2)