【SQL解惑】谜题4：门禁卡

解法一：

-- 1、创建用户表

create table personnel

( emp_id integer not null primary key ,

emp_name char ( 30 ) not null

)

--2、创建门禁卡状态表

create table Badges

(

badge_nbr integer not null primary key ,

emp_id integer not null references personnel ( emp_id ),

issued_date date not null,

badge_status char ( 1 ) not null check ( badge_status in ( 'A' , 'I' )),

)

--3、插入初始化数据

insert into personnel ( emp_id , emp_name )

values ( 1 , 'Backer' ),( 2 , 'Tal' ),( 3 , 'Cak' ),( 4 , 'Dake' )                                         

insert into Badges ( badge_nbr , emp_id , issued_date , badge_status )

values ( 1 , 1 , '2017-11-08' , 'A' ),

( 2 , 1 , '2017-11-09' , 'A' ),

( 3 , 1 , '2017-11-10' , 'A' ),

( 4 , 1 , '2017-11-11' , 'A' ),

( 5 , 1 , '2017-11-12' , 'A' ),

( 6 , 1 , '2017-11-13' , 'A' ),

( 7 , 2 , '2017-11-08' , 'A' ),

( 8 , 2 , '2017-11-09' , 'A' ),

( 9 , 2 , '2017-11-10' , 'A' ),

( 10 , 3 , '2017-11-08' , 'A' ),

( 11 , 3 , '2017-11-09' , 'A' ),

( 12 , 4 , '2017-11-08' , 'A' )

--4、更新门禁卡的状态（改为最容易理解的SQL语句）

update Badges

set badge_status = 'I'

from Badges as b3

where ( 'A' = any( select badge_status

                           from Badges as b1

                                            where b3 . emp_id = b1 . emp_id ))

       and ( issued_date < ( select max ( issued_date )

                                         from Badges as b2

                                         where b3 . emp_id = b2 . emp_id ))

将上面的SQL语句翻译一下，符合条件的是如下的数据

select *

from Badges as b3

where ( 'A' = any( select badge_status

                           from Badges as b1

                                            where b3 . emp_id = b1 . emp_id ))

       and ( issued_date < ( select max ( issued_date )

                                         from Badges as b2

                                         where b3 . emp_id = b2 . emp_id ))

--5、再次插入数据，验证UPDATE语句是否起作用。

insert into Badges ( badge_nbr , emp_id , issued_date , badge_status )

values ( 13 , 1 , '2017-11-14' , 'A' ),

( 14 , 2 , '2017-11-11' , 'A' ),

( 15 , 3 , '2017-11-10' , 'A' ),

( 16 , 4 , '2017-11-09' , 'A' )

--分解上面第3步的步骤：

（1）where子句中的第一个条件

select badge_status

from Badges as b1

where   b3 . emp_id = b1 . emp_id

解释：这个的作用是按emp_id进行分组，列举出所有badge_status门禁卡状态。

（2）将列出来的门禁状态与‘A’的门禁状态进行比较，any即存在有一个跟‘A’不一样的就返回true。

'A' = any( select badge_status

          from Badges as b1

           where Badges . emp_id = b1 . emp_id ))

（3）where子句的第二个条件

select max ( issued_date )

from Badges as b2

where   b3 . emp_id = b2 . emp_id 

解释：这个的作用是按照emp_id分组，然后筛选出最近的日期

（4）将日期根据emp_id的不同分组，在分组中筛选出最大的日期，与每个日期与每组的最大日期进行比较

issued_date < ( select max ( issued_date )

                from Badges as b2

                where Badges . emp_id = b2 . emp_id ))

（5）选择表 Badges中门禁状态只有存在一个为A的并且小于最近的日期的数据

select *

from Badges as b3

where ( 'A' = any( select badge_status

                           from Badges as b1

                                            where b3 . emp_id = b1 . emp_id ))  

       and ( issued_date < ( select max ( issued_date )

                                         from Badges as b2

                                         where b3 . emp_id = b2 . emp_id ))

（6）使用update...set...from...where的语句进行更新即可。

解法二：

--1、创建用户表与门禁卡记录表

create table personnel

( emp_id integer not null primary key ,

emp_name char ( 30 ) not null

)

                                        

create table Badges

(

badge_nbr integer not null primary key ,

emp_id integer not null references personnel ( emp_id ),

issued_date date not null,

badge_seq integer not null check ( badge_seq > 0 ), unique ( emp_id , badge_seq ),

)

--2、插入数据

insert into personnel ( emp_id , emp_name )

values ( 1 , 'Backer' ),( 2 , 'Tal' ),( 3 , 'Cak' ),( 4 , 'Dake' )

                                        

insert into Badges ( badge_nbr , emp_id , issued_date , badge_seq )

values ( 1 , 1 , '2017-11-08' , 1 ),

( 2 , 1 , '2017-11-09' , 2 ),

( 3 , 1 , '2017-11-10' , 3 ),

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

( 5 , 1 , '2017-11-12' , 5 ),

( 6 , 1 , '2017-11-13' , 6 ),

( 7 , 2 , '2017-11-08' , 1 ),

( 8 , 2 , '2017-11-09' , 2 ),

( 9 , 2 , '2017-11-10' , 3 ),

( 10 , 3 , '2017-11-08' , 1 ),

( 11 , 3 , '2017-11-09' , 2 ),

( 12 , 4 , '2017-11-08' , 1 )

--3、创建筛选出最新门禁卡的临时表

create view ActiveBadges ( emp_id , badge_nbr )

as

select  b1.emp_id , b1. badge_nbr

from Badges as b1

where badge_seq

        = ( select max ( badge_seq )

               from Badges as b2

               where b1 . emp_id = b2 . emp_id )

--4、当出现门禁卡丢失的情况时，更新门禁卡的最新顺序

update Badges

set badge_seq

  = ( select count (*)

               from Badges as b1

               where Badges . emp_id = b1 . emp_id

               and Badges . badge_seq >= b1 . badge_seq )

通过按emp_id进行分组，再接着将不同日期的门禁卡对应的序列号与自己比较，接着计算出小于自己的门禁卡的数量，该数量即是正确的新的序列号。