Oracle decode函数处理复杂报表

员工10月考勤总汇表

行号 工号 姓名 迟到 旷工 合 请假 事假
1 100 张三 1 2 7 7 4
2 100 李四 0 0
3 100 王五 3 2 0 0
4 101 杨六 5 1 6 2 2


表格设计一： 方案一

完全按照汇总表来设计：
分析：如果客户要求增加新的考勤类型，例如早退，目前这种设计也要必须改动表达结构 的结构
但是，表达结构是绝对不能够动的，

结论：这种设计是不科学的


关键：如果增加字段‘’ 早退，你的数据库表会重新设计吗？
在 设计方案一中会重新设计表达结构，不可能为每一个报表专门建一张表。


表格设计二

员工表 考勤类型表

工号 考勤编号
姓名 名称


考勤表
流水号
工号
考勤编号
日期

员工表t_emp 考勤类型表 t_checktype

eid ename esex type_id type_name
100 郭靖 1 1 迟到
101 杨过 1 2 矿工
102 黄蓉 2 3 病假
103 小龙女 2


特别注意：在报表开发中，1是迟到，定下来以后不能改变


考勤表 t_check (记录每人每天都考勤情况)

eid check_date type_id
100 2007-11-01 1
101 2007-11-01 2
102 2007-11-03 2
103 2007-11-04 3

根据汇总的情况，应该增加4个列：所以现在就是统计出各种考勤的次数，统计出迟到
的次数，矿工、事假、病假

因为报表都与时间有关、所以统计时不要忘记时间限制


一、建表语句

CREATE TABLE t_emp
(
eid NUMBER PRIMARY KEY,
ename VARCHAR2(20),
esex VARCHAR2(4)
)


CREATE TABLE t_checktype(
type_typeid NUMBER PRIMARY KEY,
type_name VARCHAR2(20)

)


CREATE TABLE t_check(
eid NUMBER ,
check_date DATE ,
type_typeid NUMBER,
FOREIGN KEY (eid)REFERENCES t_emp(eid),
FOREIGN KEY (type_typeid)REFERENCES t_checktype(type_typeid)
)


--添加exce里测试数据的一种方法
SELECT t.* ,ROWID FROM t_emp t;
SELECT t.* ,ROWID FROM t_check t;
SELECT t.* ,ROWID FROM t_checktype t;

--设计2：添加探亲的功能
SELECT t.eid,
sum(decode(t.type_typeid,1,1,0))迟到 ,
sum(decode(t.type_typeid,2,1,0))矿工,
SUM(decode(t.type_typeid,3,1,4,1,0))合计,
sum(decode(t.type_typeid,3,1,0))病假,
sum(decode(t.type_typeid,4,1,0))事假,
sum(decode(t.type_typeid,5,1,0))探亲
FROM t_check t
WHERE to_char(t.check_date,'yyyy.mm')=2007.11
GROUP BY t.eid;
--在这种情况下，利用decode函数，报表改动时（基于报表通常不轻易改变，
-- 只需要对数据和sql改一下就可以了，表的结构还是没有变




--在实际工作中，如果对上面的建立视图，则包含了日期，一般建立视图
--都要把范围建立的更广一点




CREATE OR REPLACE VIEW v_check
AS
SELECT t.eid,
sum(decode(t.type_typeid,1,1,0))迟到 ,
sum(decode(t.type_typeid,2,1,0))矿工,
SUM(decode(t.type_typeid,3,1,4,1,0))合计,
sum(decode(t.type_typeid,3,1,0))病假,
sum(decode(t.type_typeid,4,1,0))事假,
sum(decode(t.type_typeid,5,1,0))探亲
FROM t_check t
GROUP BY t.eid;

SELECT * FROM v_check

--设计3：要增加显示姓名，则将员工表和视图表连接就可以了
SELECT * FROM
t_emp e, v_check c
WHERE e.eid = c.eid;