sql解惑- 缺勤者问题

[/color][color=darkred][color=darkred][/color]问题： 这个问题是有Jim chupella 在CompuServe上的MS Access论坛上提出的。 他需要创建一个记录雇员缺勤率的数据库。使用的表

create table Absenteeism (
emp_id integer not null  references personel(emp_id),
absent_date date not null,
reason_code varchar2(40) not null references ExcuseList(reason_code),
severity_points integer not null check(severity_points between 1 and 4),
primary key(emp_id,absent_date)
);

  雇员的id号用来标识每个雇员。 reason＿code是相关缺勤记录原因的简短文本描述(例如，被运送啤酒的卡车撞了，;今天不顺利，心情很糟糕，等等)，它来自于一个不断增加增加的虚构列表。严重性积分severity_points）时候一个计分系统，对缺勤行为进行处罚性计分。
   如果雇员在一年的时间内严重性计分累计达到40分，就自动将该雇员解雇。 如果雇员连续缺勤超过一个天，就视为长病假， 而不是普通的缺勤。这时候第二天、第三天和以后的日子都
不会统计该雇员的严重性分数，这些天也不算做缺勤。
你的工作是编写实施的这两个业务规则的SQL， 如果需要也可以该模式


解答：看一看解雇员工的第一条规定， 最常见的设计错误是试图从表中删除第二、三天以及以后的天数。 这个方法使计算天数的查询变得混乱， 并很难查找从表中连续的病假
。 

首先要更改严重性惩罚分数（severity_points）可以为0， 这样可以使Absenteeism表中的记录雇员的长病期。只需要将severity_points改为check( severity_points between 0 and 4)
step1.创建一个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,'谢江');

step2.创建一个ExcuseList原因执行表

create table ExcuseList(
   reason_code varchar2(40) primary key not null,
   effective_date date not null
);

//表已经创建

insert into ExcuseList  values('long term illness','01-1月-2009');
insert into ExcuseList  values('absentess','01-1月-2009');
insert into ExcuseList  values('on a bussiness trip','01-1月-2009');
commit;

step3.创建一个缺勤表

create table Absenteeism (
emp_id integer not null  references personel(emp_id) on delete cascade,
absent_date date not null,
reason_code varchar2(40) not null references ExcuseList(reason_code),
severity_points integer not null check(severity_points between 0 and 4),
primary key(emp_id,absent_date)
);

由于在开除员工，为让倚赖它的表产生数据统一性，必须在emp_id上加入 on delete cascade, 修改severity_points的check约束 在0，4之间, 不管用alter table absenteeism modify (severity_points integer check(severity_points between 0 and 4));
插入数据
使用了insert into table_name (col_1,col2,...) (select col_1 ,select col_2 from (select col_1,col_2 from table_nam2 ))

由于absenteeism表没有数据，而且插入的数据与时间有关系。absenteeism中emp_id来来自personel表的emp_id,absent_date于时间有关系（要求absent_date不能在同一个用户产生的相同的日期）,reason_code来自ExcusteList表中，severity_points是在0和4之间的整数

create or replace procedure proc_Absenteeism_insert
as
start_date date;
begin
start_date:=sysdate;
for i in 0..9999
loop
    insert into
    Absenteeism (emp_id,absent_date,reason_code,severity_points)
    (
    select emp_id ,insdate,reason_code,spoints 
    from (    
        select p.emp_id,to_date('1-3-2009','dd-mm-yyyy')+i insdate,
        e.reason_code ,
        trunc(dbms_random.value(0,5)) spoints
        from 
        personel p ,ExcuseList e 
        order by dbms_random.value 
   ) 
   where rownum<2
);
end loop;
end;

//执行procedure

exec proc_absenteeism_insert;

引用

用时6秒

step4. 更改长病期和删除超过40分的雇员

更新数据要求连续两次或这迟到两次以上视为病假，并且为长病假，而不是普通的缺勤。
一个相同的表使用子查询 a1.id=a2.id 关联起来， 然后使用‘第二天、第三天和以后的日子都不会统计该雇员的严重性分数’ 条件， 即是’每个人前后相差一天‘

update absenteeism a1
set severity_points=0,
   reason_code='long term illness'
where exists
    (select * from absenteeism a2 where    a1.emp_id=a2.emp_id and a1.absent_date-1=a2.absent_date);

已经更新2478行

由于插入数据时概率过大，把严重性惩罚分数40， 改为130分

delete * from personel  where emp_id in(
select emp_id from absenteeism  where absent_date between sysdate  and 
add_months(sysdate,12) group by emp_id having
sum(severity_points)>=130
);