sql解惑-跟踪投资组合问题

    这个谜底是给你的。 或许我是只见树木， 不见森林， 但是如果想以一种不会导致无数循环引用的优雅方式来解决问题这个问题， 似乎并不容易。
    这个谜底似乎是一个关于完整的系统， 但我的问题是： 是否存在一个方法， 在表设计阶段消除明显的循环引用。
    你必须记录一个组织中的投资问题， 以备查找、检索。 这个投资组合有很多附加属性， 这里只列出一些与此谜题相关的属性：  
create table Portfolios
(
file_id integer not null primary key,
issue_date date not null,
superseded_file_id integer not null references Portfolios(file_id),
supersedes_file_id integer not null references Portfolios(file_id)
);
下面的谜底：
1.需要记录那一个投资组合代替了当前的投资组合
2.需要记录这个投资组合替代的是那一种投资组合
3.需要能够恢复某一个投资组合(这个做的结论会替代一个或一系列投资组合， 并导致循环引用。)
4.因为issue_date的存在，能够记录日期， 但那时如果恢复某个投资组合，那就又一个难题！
5.不论select语句上的投资组合是什么， 都能够select出最新的投资组合。
6.需要能够对一些的文档重新生成审计追踪。

方法#1
因为在谜底的说明中无意表达式“前驱”和“后驱”的说法， 所以可知这个问题是处理序数计数的。让我们应用已知的嵌套集合代码代替它。
    首先， 创建一个表， 容纳每个文件的所有信息：
    create table Portfolios(
        file_id  integer not null primary key,
        other_stuff varchar2(40) not null
    );
向Profolios表中插入数据

insert into portfolios values(222,'stuff');
insert into portfolios values(223,'old stuff');
insert into portfolios values(224,'new stuff');
insert into portfolios values(225,'borrowed stuff');
insert into portfolios values(322,'blue stuff');
insert into portfolios values(323,'purple stuff');
insert into portfolios values(324,'red stuff');
insert into portfolios values(325,'green stuff');
insert into portfolios values(999,'yellow stuff');

然后创建一个表容纳文档的后继，并包含两个特殊的列： chain 和next

create table Succession(
    chain integer not null ,
    next integer default 0 not null,
    file_id integer not null references Portfolios(file_id),
    suc_date date not null,
    primary key(chain, next)
);

向succession表中插入数据

insert into Succession values(1,(select count(*) from Succession where chain=1),222,to_date('2007-11-1','yyyy-mm-dd'));
insert into Succession values(1,(select count(*) from Succession where chain=1),223,to_date('2007-11-2','yyyy-mm-dd'));
insert into Succession values(1,(select count(*) from Succession where chain=1),224,to_date('2007-11-4','yyyy-mm-dd'));
insert into Succession values(1,(select count(*) from Succession where chain=1),225,to_date('2007-11-5','yyyy-mm-dd'));
insert into Succession values(1,(select count(*) from Succession where chain=1),999,to_date('2007-11-25','yyyy-mm-dd'));

insert into Succession values(2,(select count(*) from Succession where chain=2),322,to_date('2007-11-1','yyyy-mm-dd'));
insert into Succession values(2,(select count(*) from Succession where chain=2),323,to_date('2007-11-2','yyyy-mm-dd'));
insert into Succession values(2,(select count(*) from Succession where chain=2),324,to_date('2007-11-4','yyyy-mm-dd'));

insert into Succession values(2,(select count(*) from Succession where chain=2),322,to_date('2007-11-5','yyyy-mm-dd'));
insert into Succession values(2,(select count(*) from Succession where chain=2),323,to_date('2007-11-12','yyyy-mm-dd'));
insert into Succession values(2,(select count(*) from Succession where chain=2),999,to_date('2007-11-25','yyyy-mm-dd'));

选出最新组合,无论组合怎样变化，我都能选出最新的组合:通过某个组合的max（next）找到file_id, 然后找到Portfolios就可以

select distinct * from Succession s, Portfolios p
where s.file_id =p.file_id and next=
(select max(next) from Succession s2
where s.chain=s2.chain);

完成审计功能: 通过chain， next排序就可以得到新顺序的投资计划了

select chain , next ,p1.file_id, other_stuff , suc_date
from succession s1,portfolios p1
where s1.file_id=p1.file_id
order by chain, next;

记录这个投资组合组合替代哪个投资组合

select s1. file_id , f2.file_id
from succession s1, succession s2
where s1.chain=s2.chain
and s1.next=s2.next+1 and s1.file_id=999;