【SQL解惑】谜题12：索赔状态

解惑一：

1、创建表和插入数据源

create table Claims ( claim_id int not null primary key ,

patient_name varchar ( 10 ))

create table Defendants ( claim_id int not null references Claims ( claim_id ),

defendant_name varchar ( 10 )not null )

create table LegalEvents ( claim_id int not null references Claims ( claim_id ),

defendant_name varchar ( 10 ),

claim_status varchar ( 10 ),

change_date date )

create table ClaimStatusCodes ( claim_status varchar ( 10 ) not null,

claim_statuss_desc varchar ( 40 ),

claim_seq int not null)

insert into Claims values ( 10 , 'Smith' ),( 20 , 'Jones' ),( 30 , 'Brown' ),( 40 , 'Lims' )

insert into Claims valuesinsert into Defendants values ( 10 , 'Johnson' ),( 10 , 'Meyer' ),( 10 , 'Dow' ),

( 20 , 'Baker' ),( 20 , 'Meyer' ),( 30 , 'Johnson' ),( 40 , 'Mona' ),( 40 , 'Smel' ),( 40 , 'Demal' )

insert into LegalEvents values ( 10 , 'Johnson' , 'AP' , '1994-01-01' ),

( 10 , 'Johnson' , 'OR' , '1994-02-01' ),

( 10 , 'Johnson' , 'SF' , '1994-03-01' ),

( 10 , 'Johnson' , 'CL' , '1994-04-01' ),

( 10 , 'Meyer' , 'AP' , '1994-01-01' ),

( 10 , 'Meyer' , 'OR' , '1994-02-01' ),

( 10 , 'Meyer' , 'SF' , '1994-03-01' ),

( 10 , 'Dow' , 'AP' , '1994-01-01' ),

( 10 , 'Dow' , 'OR' , '1994-02-01' ),

( 20 , 'Meyer' , 'AP' , '1994-01-01' ),

( 20 , 'Meyer' , 'OR' , '1994-02-01' ),

( 20 , 'Baker' , 'AP' , '1994-01-01' ),

( 30 , 'Johnson' , 'AP' , '1994-01-01' ),

( 40 , 'Mona' , 'AP' , '1994-01-01' ),

( 40 , 'Mona' , 'OR' , '1994-02-01' ),

( 40 , 'Mona' , 'SF' , '1994-03-01' ),

( 40 , 'Smel' , 'AP' , '1994-02-01' ),

( 40 , 'Smel' , 'OR' , '1994-03-01' ),

( 40 , 'Demal' , 'AP' , '1994-03-01' )

insert into ClaimStatusCodes values ( 'AP' , 'Awaiting review panel' , 1 ),

( 'OR' , 'Panel opinion rendered' , 2 ),

( 'SF' , 'Suit filed' , 3 ),

( 'CL' , 'Closed' , 4 )

2、筛选语句

思路：先按patient_name分组内选择最大的claim_seq再在claim_id中选择最小的claim_seq

select *

  from Claims as c1 , ClaimStatusCodes as s1

  where s1 . claim_seq

    in ( select MIN ( s2 . claim_seq )

          from ClaimStatusCodes as s2

        where s2 . claim_seq

            in ( select MAX ( s3 . claim_seq )

                 from LegalEvents as e1 , ClaimStatusCodes as s3

                where e1 . claim_status = s3 . claim_status

                  and e1 . claim_id = c1 . claim_id

                group by e1 . defendant_name ))

解惑二：

select e1 . claim_id , c1 . patient_name , e1 . claim_status

from LegalEvents as e1 , Claims as c1

where e1 . claim_id = c1 . claim_id

group by e1 . claim_id , c1 . patient_name , e1 . claim_status

解惑三：

select c1 . claim_id , c1 . patient_name ,

             case MIN ( s1 . claim_seq )

             when 2 then 'AP'

             when 3 then 'OR'

             when 4 then 'SF'

             else 'CL' end

from

(( Claims as c1

inner join Defendants as d1 on c1 . claim_id = d1 . claim_id )

cross join ClaimStatusCodes as s1 )

left outer join LegalEvents as e1 on c1 . claim_id = e1 . claim_id

and d1 . defendant_name = e1 . defendant_name

and s1 . claim_status = e1 . claim_status

where e1 . claim_id is null

group by c1 . claim_id , c1 . patient_name

解决步骤：

（1）将病人表与被告表联合起来，查看每个病人提出索赔的被告

Claims as c1

inner join Defendants as d1 on c1 . claim_id = d1 . claim_id )

（2）将上述的表与索赔状态联合起来，查看每个病人提出索赔的被告的所有索赔状态

Claims as c1

inner join Defendants as d1 on c1 . claim_id = d1 . claim_id )

cross join ClaimStatusCodes as s1

（3）将上述的表与索赔状态历史联合起来，同时进行右外连接，查看每个病人提出索赔的被告的所有未完成的索赔状态

left outer join LegalEvents as e1 on c1 . claim_id = e1 . claim_id

and d1 . defendant_name = e1 . defendant_name

and s1 . claim_status = e1 . claim_status

where e1 . claim_id is null

（4）将上述的表内容按病人ID和病人名字进行分组，然后获取索赔状态表中的最小值，再通过最小值进行判断。

如果最小值为2，那么对应的每个病人提出索赔的被告的所有已完成的索赔状态则为2的上一个1，即‘AP’

如果最小值为3，那么对应的每个病人提出索赔的被告的所有已完成的索赔状态则为3的上一个2，即‘OR’

如果最小值为4，那么对应的每个病人提出索赔的被告的所有已完成的索赔状态则为4的上一个3，即‘SF’

如果以上都不满足，则代表对应的每个病人提出索赔的被告的所有已完成的索赔状态则为4，即‘CL’