PB中crosstab表继承上个tab中数据的问题
比如我现在的结果是(现在是动态列为年份,值是amount):
2006 2007 2008 2009
amount amount amount amount
3000 5000
可以看出2007和2009的数据为null,那么如果我要2007继承2006的amount数据,即3000;2009继承2008的5000,如何办到?
也就是说如果一年的amount为null,就自动继承前一年的数据。
表可以是静态也可以是动态的,在下是新手,请高手指教。
[解决办法]
//测试数据
create table tb_xx(
company varchar(10) not null ,
amount numeric(10,2) null ,
year numeric(4) not null,
primary key(company,year)
);
insert into tb_xx(company,amount,year)
SELECT 'COMPANY A',3000,2006
union
SELECT 'COMPANY A',5000,2008
union
SELECT 'COMPANY B',2000,2007
union
SELECT 'COMPANY B',8000,2009;
//呵呵,查询的SQL
select c.company,b.year,amount=(select top 1 amount from tb_xx where company=c.company and year<=b.year order by year desc)
from tb_xx a,(select distinct year from tb_xx) b,(select distinct company from tb_xx)c
where a.year=*b.year and a.company=*c.company
order by c.company,b.year