【SQL解惑】谜题7：跟踪投资组合

解惑一：

--创建数据源

create table Porfolios

(

       file_id_1 integer not null primary key ,

       stuff_1 char ( 15 ) not null

)

insert into Porfolios ( file_id_1 , stuff_1 )

values

( 222 , 'stuff' ),

( 223 , 'old stuff' ),

( 224 , 'new stuff' ),

( 225 , 'borrowed stuff' ),

( 322 , 'blue stuff' ),

( 323 , 'purple stuff' ),

( 324 , 'red stuff' ),

( 325 , 'green stuff' ),

( 999 , 'yellow stuff' )

create table Succession

(

       chain integer not null,

       next_1 integer not null,

       file_id_1 integer not null references Porfolios ( file_id_1 ),

       suc_date date not null,

        primary key ( chain , next_1 )

)

insert into Succession ( chain , next_1 , file_id_1 , suc_date )

values

( 1 , 0 , 222 , '2017-11-01' ),

( 1 , 1 , 223 , '2017-11-02' ),

( 1 , 2 , 224 , '2017-11-04' ),

( 1 , 3 , 225 , '2017-11-05' ),

( 1 , 4 , 999 , '2017-11-25' ),

( 2 , 0 , 322 , '2017-11-01' ),

( 2 , 1 , 323 , '2017-11-02' ),

( 2 , 2 , 324 , '2017-11-04' ),

( 2 , 3 , 322 , '2017-11-05' ),

( 2 , 4 , 323 , '2017-11-12' ),

( 2 , 5 , 999 , '2017-11-25' )

---不论select语句上的投资组合是什么，都能够select出最新的投资组合

select distinct p1 . file_id_1 , stuff_1 , suc_date

from Porfolios as p1 , Succession as s1

where p1 . file_id_1 = s1 . file_id_1

and next_1 = ( select max ( next_1 ) from Succession as s2 where s1 . chain = s2 . chain )

----需要能够对一系列文档重新生成审计痕迹

select chain , next_1 , p1 . file_id_1 , stuff_1 , suc_date

from Porfolios as p1 , Succession as s1

where s1 . file_id_1 = p1 . file_id_1

order by chain , next_1

----需要记录这个投资组合替代的是哪一个投资组合

begin

declare @my_file_id integer   ---声明一个需要查询的文档号

select s1 . file_id_1 , ' superseded ' , s2 . file_id_1 , ' on ' , s2 . suc_date

from Succession as s1 , Succession as s2

where s1 . chain = s2 . chain

and s1 . next_1 = s2 . next_1 + 1

and s1 . file_id_1 = @my_file_id

end

----需要能够恢复某个投资组合

begin

declare @old_file_id integer    ----声明一个旧文档号

declare @my_file_id integer    ----声明一个新文档号

declare @new_suc_date date      ----声明一个新文档号的日期

insert into Porfolios values ( @my_file_id , 'stciky stuff' )

insert into Succession ( chain , next_1 , file_id_1 , suc_date )

values (( select distinct chain

                 from Succession as s1

                where s1 . file_id_1 = @old_file_id ),

               ( select max ( next_1 ) + 1

                 from Succession as s1

                where s1 . chain = ( select distinct chain

                                                  from Succession as s2

                                                 where file_id_1 = @my_file_id )),

                                                 @my_file_id , @new_suc_date )

end