[color=darkred][/color][color=darkred][/color][color=darkred][/color][b]问题: 这个问题是有Jim chupella 在CompuServe上的MS Access论坛上提出的。 他需要创建一个记录雇员缺勤率的数据库。使用的表[/b]
雇员的id号用来标识每个雇员。 reason_code是相关缺勤记录原因的简短文本描述(例如,被运送啤酒的卡车撞了,;今天不顺利,心情很糟糕,等等),它来自于一个不断增加增加的虚构列表。严重性积分severity_points)时候一个计分系统,对缺勤行为进行处罚性计分。
如果雇员在一年的时间内严重性计分累计达到40分,就自动将该雇员解雇。 如果雇员连续缺勤超过一个天,就视为长病假, 而不是普通的缺勤。这时候第二天、第三天和以后的日子都
不会统计该雇员的严重性分数,这些天也不算做缺勤。
你的工作是编写实施的这两个业务规则的SQL, 如果需要也可以该模式
[b]解答:看一看解雇员工的第一条规定, 最常见的设计错误是试图从表中删除第二、三天以及以后的天数。 这个方法使计算天数的查询变得混乱, 并很难查找从表中连续的病假[/b]
。
首先要更改严重性惩罚分数(severity_points)可以为0, 这样可以使Absenteeism表中的记录雇员的长病期。只需要将severity_points改为check( severity_points between 0 and 4)
[b]step1.创建一个Personel员工表[/b]
[quote]//alter table personel add constraints PK_personel_emp primary key(emp_id);
[/quote]
[b]step2.创建一个ExcuseList原因执行表[/b]
//表已经创建
[b]step3.创建一个缺勤表[/b]
由于在开除员工,为让倚赖它的表产生数据统一性,必须在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之间的整数
[quote]用时6秒[/quote]
[b]step4. 更改长病期和删除超过40分的雇员[/b]
更新数据要求连续两次或这迟到两次以上视为病假,并且为长病假,而不是普通的缺勤。
一个相同的表使用子查询 a1.id=a2.id 关联起来, 然后使用‘第二天、第三天和以后的日子都不会统计该雇员的严重性分数’ 条件, 即是’每个人前后相差一天‘
已经更新2478行
由于插入数据时概率过大,把严重性惩罚分数40, 改为130分
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, 如果需要也可以该模式
[b]解答:看一看解雇员工的第一条规定, 最常见的设计错误是试图从表中删除第二、三天以及以后的天数。 这个方法使计算天数的查询变得混乱, 并很难查找从表中连续的病假[/b]
。
首先要更改严重性惩罚分数(severity_points)可以为0, 这样可以使Absenteeism表中的记录雇员的长病期。只需要将severity_points改为check( severity_points between 0 and 4)
[b]step1.创建一个Personel员工表[/b]
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;
[quote]//alter table personel add constraints PK_personel_emp primary key(emp_id);
[/quote]
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,'谢江');
[b]step2.创建一个ExcuseList原因执行表[/b]
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;
[b]step3.创建一个缺勤表[/b]
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;
[quote]用时6秒[/quote]
[b]step4. 更改长病期和删除超过40分的雇员[/b]
更新数据要求连续两次或这迟到两次以上视为病假,并且为长病假,而不是普通的缺勤。
一个相同的表使用子查询 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
);