sql解惑- 门禁卡

由于你们公司人员的合理精简，你吸纳在身兼安全主管和数据库管理员。你想要产生一个员工及其有效的门禁卡的列表。 取决于每个雇员现在几个场所工作，
他可以有多个门禁卡，但是一次只能够有一个有效的。因为最新的门禁卡在新工作场所发出的，所以默认该卡是有效的。为了防止仿照， 门禁卡号是随机的。
你的任务是产生一个员工列表， 每个员工都是需要有关联的有效门禁卡。 使用A表示有效（Active）， I表示失效（Inactive）。
[b]方法一：[/b]
删除personel表

drop table personel;
create table personel(
emp_id integer not null primary key,
emp_name varchar2(40) not null
);
create sequence personel_sequence 
increment by 1
start with 1
nomaxvalue 
nocycle
cache 10;
//alter table personel add constraints PK_personel_emp primary key(emp_id);
insert into personel values(personel_sequence.nextval,'liaobinxu');
insert into personel values(personel_sequence.nextval,'fenerdong');
insert into personel values(personel_sequence.nextval,'孙翔');
insert into personel values(personel_sequence.nextval,'谢江');

创建门禁卡 Badges
drop table Badges;
create table Badges(
badge_id integer not null primary key,
emp_id integer not null references personel(emp_id),
issued_date date not null,
badge_status char(1) not null check(badge_status in('A','I')),
check (1<all(select count(badge_status) from badges where badge_status='A' group by emp_id))
);
[quote] check (1--ORA-02251: 此处不允许子查询 所以表创建失败[/quote]很多sql产品不支持表级约束使用select约束， oracle也同样, 所以去掉最后那个check

create table Badges(
    badge_id integer not null primary key,
    emp_id integer  not null references personel(emp_id),
    issued_date date not null,
    badge_status char(1) not null check(badge_status in('A','I'))
);

插入数据

create sequence badge_sequence
increment by 1
start with 1
nomaxvalue
nocycle
nocache;

插入 badge_id 为badge_sequence序列的nextval， issued_date为sysdate加上0-30之间的随机数

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'I');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'A');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'A');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'A');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'A');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'A');

insert into Badges(badge_id, emp_id , issued_date,Badge_Status)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'A');

使用你一种方法将最近的门禁卡状态设为‘A’

查看状态

select badge_id, emp_id, to_char(issued_date,'yyyy-mm-dd hh24:mi:ss'),badge_status from badges order by emp_id;

[i] BADGE_ID EMP_ID TO_CHAR(ISSUED_DATE,'YYYY-MM-D BADGE_STATUS

--------------------------------------- --------------------------------------- ------------------------------ ------------

9 1 2009-03-30 19:04:32 A

4 1 2009-04-14 19:04:32 I

3 1 2009-04-01 19:04:32 I

8 2 2009-03-29 19:04:32 A

10 2 2009-04-19 19:04:32 A

2 2 2009-04-08 19:04:32 I

12 2 2009-04-12 19:04:33 A

6 3 2009-04-16 19:04:32 I

5 3 2009-03-24 19:04:32 I

1 3 2009-03-29 19:04:32 I

6 3 2009-03-25 19:04:32 I

11 3 2009-04-15 19:04:32 A

7 4 2009-04-03 19:04:32 A



13 rows selected[/i]

为了构建更新的条件

update badges set badge_status='I';
update Badges set badge_status ='A'where ('I'=all(select badge_status from badges b1 where Badges.emp_id =b1.emp_id))and (issued_date=(select  max(issued_date) from badges b2 where badges.emp_id =b2.emp_id))

查看更新结果

select * from badges order by emp_id, issued_date; 

[i] BADGE_ID EMP_ID ISSUED_DATE BADGE_STATUS

--------------------------------------- --------------------------------------- ----------- ------------

2 1 2009/3/25 1 A

5 2 2009/3/30 1 I

12 2 2009/4/6 19 I

6 2 2009/4/8 19 I

3 2 2009/4/18 1 A

7 3 2009/4/11 1 I

4 3 2009/4/20 1 A

8 4 2009/3/29 1 I

10 4 2009/3/31 1 I

1 4 2009/4/7 19 I

9 4 2009/4/8 19 I

13 4 2009/4/9 19 I

11 4 2009/4/12 1 A



13 rows selected[/i]
[b]方法2 [/b]另一方法是为门禁卡分配一顺序号，并使用min（）和max（）顺序号作为有效的门禁卡
删除badges

drop table badges;
drop sequence badge_sequence;
--插入数据
create sequence badge_sequence
increment by 1
start with 1
nomaxvalue
nocycle
nocache;

--另外创建一个badges表， 因为判断badge状态的badge_status ，该为badge_seq ,并使用min（）和max（）顺序号作为有效的门禁卡

create table badges
(
    badge_id integer not null primary key ,
    emp_id integer not null references personel(emp_id),
    issued_date date not null,
    badge_seq integer not null check(badge_seq>0),
    unique(emp_id, badge_seq)
);
create sequence badge_increment_sequence 
increment by 1
start with 1
nomaxvalue
nocycle
noorder;
--插入 badge_id 为badge_sequence序列的nextval，
-- issued_date为sysdate加上0-30之间的随机数
insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert
into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),'badge_increment_sequence.nextval);

insert into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

insert into Badges(badge_id, emp_id , issued_date,Badge_seq)
values(badge_sequence.nextval,round(dbms_random.value(1,4)),sysdate+round(dbms_random.value(0,30)),badge_increment_sequence.nextval);

现在创建视图以显示有效的门禁卡：

create view ActiveBadges(emp_id, badge_id)
as 
select emp_id, badge_id
from badges b1
where b1.badge_seq=
(select max(badge_seq) from badges b2
where b1.emp_id =b2.emp_id 
);

但门禁卡丢失后， 这个方法需要执行更新操作以复位序列号。对于这个查询不需要这样做， 但是看到序号排列好一些，而且也容易躁动每一雇员的卡总数。

update badges 
set badge_seq=(
select count(*) from badges b1 
where badges.emp_id =b1.emp_id and badges.badge_seq>=b1.badge_seq
);

这个update的意思， 同一个员工下， 相同的数据进行比较， 取个数update之前的数据

select * from badges order by badge_id, emp_id;

--update之后的数据
select * from badges order by badge_id, emp_id;