创建一个虚拟SQL表来进行数值比对

日期： 2012-01-09 作者：Dan Clamage 来源：TechTarget中国 英文

问：我有两个表：- emp_info和emp_attendance 　　表结构如下： Emp_Info    empid int, empname varchar(50) Emp_Attendance    empid int, emp_day datetime The emp_attendance has the entries of dates for all the em……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

问：我有两个表：- emp_info和emp_attendance

　　表结构如下：

Emp_Info
   empid int, empname varchar(50)

Emp_Attendance
   empid int, emp_day datetime
The emp_attendance has the entries of dates for all the employees when they are present like:
Empid    emp_day
101        1-1-2012
101        1-2-2012
101        1-4-2012 //Note: third date is missing as the employee is absent
101        1-5-2012  
102        1-1-2012
102        1-3-2012 //Note: Here employee 102 is absent on date 2

　　我现在希望得到所有员工缺席日期的列表，请问该如何实现？谢谢！

　　答：现在唯一缺少的，就是DATE表来生成给定范围内的所有日期。

　　一些人喜欢创建一个真实表，然后用所有日期来进行填充，但这显然不是一个好方法。所以创建一个虚拟表更加简单一些，管道(Pipelined)函数在应对这个问题时可以很好地发挥它的作用。

　　任何一个函数都会返回一个真实的SQL数据类型，Oracle允许我们定义一个合适的架构级别对象，来满足那些更加复杂的需求。我们需要一个管道函数的类型集合，以便返回数据。而在你的情况下，这个可以使一个简单的日期集合：

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/
My function will return this collection type.
CREATE OR REPLACE FUNCTION generate_dates(
 p_from IN DATE
,p_to IN DATE)
RETURN nt_date PIPELINED
IS
 -- normalize inputs to be as-of midnight
 v_from DATE := TRUNC(NVL(p_from, SYSDATE));
 v_to DATE := TRUNC(NVL(p_to, SYSDATE));
BEGIN
 LOOP
   EXIT WHEN v_from > v_to;
   PIPE ROW (v_from);
   v_from := v_from + 1; -- next calendar day
 END LOOP;
 RETURN;
END generate_dates;
/

　　让我们先测试一下：

-- so we can see the time too
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT *
FROM TABLE(generate_dates(SYSDATE, SYSDATE));
COLUMN_VALUE
--------------------
08-JAN-2012 00:00:00

　　注意系统生成的列名是COLUMN_VALUE，如果你觉得自己定义一个列名更好的话，那然后先创建一个对象类型，然后再根据它来定义集合。

CREATE OR REPLACE TYPE obj_date IS OBJECT (
 date_val DATE
);
/
CREATE OR REPLACE TYPE nt_date IS TABLE OF obj_date;
/
CREATE OR REPLACE FUNCTION generate_dates(
 p_from IN DATE
,p_to IN DATE)
RETURN nt_date PIPELINED
IS
 -- normalize inputs to be as-of midnight
 v_from DATE := TRUNC(NVL(p_from, SYSDATE));
 v_to DATE := TRUNC(NVL(p_to, SYSDATE));
BEGIN
 LOOP
   EXIT WHEN v_from > v_to;
   PIPE ROW (obj_date(v_from));
   v_from := v_from + 1; -- next calendar day
 END LOOP;
 RETURN;
END generate_dates;
/

　　注意，PIPE ROW命令现在需要返回对象类型，而不是一个数值类型。

　　SELECT *
　　FROM TABLE(generate_dates(SYSDATE, SYSDATE));
　　DATE_VAL
　　--------------------
　　08-JAN-2012 00:00:00

　　更多的测试案例：

SELECT *
FROM TABLE(generate_dates(SYSDATE, SYSDATE-1));
no rows selected
SELECT *
FROM TABLE(generate_dates(SYSDATE, SYSDATE+3));
DATE_VAL
--------------------
08-JAN-2012 00:00:00
09-JAN-2012 00:00:00
10-JAN-2012 00:00:00
11-JAN-2012 00:00:00
SELECT *
FROM TABLE(generate_dates(SYSDATE, NULL));
DATE_VAL
--------------------

08-JAN-2012 00:00:00


　　没有NVL的情况下，这个函数是无法跳出循环的。

　　现在我们可以开始解决你要的问题，可以找出在出席表中无记录的数据，首先创建表然后进行填充：

CREATE TABLE emp_info(
 empid INTEGER
,empname VARCHAR2(50)
);
CREATE TABLE emp_attendance(
 empid INTEGER
,emp_day DATE
);
INSERT INTO emp_info (empid, empname) VALUES (101, 'Dan Clamage');
INSERT INTO emp_info (empid, empname) VALUES (102, 'Ronak Khandelwal');
INSERT INTO emp_attendance (empid, emp_day) VALUES (101, DATE '2012-01-01');
INSERT INTO emp_attendance (empid, emp_day) VALUES (101, DATE '2012-01-02');
INSERT INTO emp_attendance (empid, emp_day) VALUES (101, DATE '2012-01-04');
INSERT INTO emp_attendance (empid, emp_day) VALUES (101, DATE '2012-01-05');
INSERT INTO emp_attendance (empid, emp_day) VALUES (102, DATE '2012-01-01');
INSERT INTO emp_attendance (empid, emp_day) VALUES (102, DATE '2012-01-03');
COMMIT;

　　针对日期值，我倾向于使用ANSI标准格式。

　　你要寻找的是每个员工的应该出席的日期集，再减去实际出席的日期。SQL可以应对数据集，所以可以直接地描述每一个数据集。下面我们来看看第一个集合：

-- potential attendance dates for each employee in a given range
SELECT ei.empid, gd.date_val
FROM emp_info ei
CROSS JOIN
TABLE(generate_dates(DATE '2012-01-01', DATE '2012-01-05')) gd
ORDER BY ei.empid, gd.date_val;
    EMPID DATE_VAL
---------- --------------------
      101 01-JAN-2012 00:00:00
      101 02-JAN-2012 00:00:00
      101 03-JAN-2012 00:00:00
      101 04-JAN-2012 00:00:00
      101 05-JAN-2012 00:00:00
      102 01-JAN-2012 00:00:00
      102 02-JAN-2012 00:00:00
      102 03-JAN-2012 00:00:00
      102 04-JAN-2012 00:00:00
      102 05-JAN-2012 00:00:00
10 rows selected.

　　注意交叉连接计算了两个集合的笛卡尔积，这可能会返回一个数量巨大的行。

　　现在来看看实际出席情况：

-- actual attendance dates for each employee in a given range
SELECT ei.empid, ea.emp_day
FROM emp_info ei
INNER JOIN emp_attendance ea
ON ea.empid = ei.empid
WHERE ea.emp_day BETWEEN DATE '2012-01-01' AND DATE '2012-01-05'
ORDER BY ei.empid, ea.emp_day;
    EMPID EMP_DAY
---------- --------------------
      101 01-JAN-2012 00:00:00
      101 02-JAN-2012 00:00:00
      101 04-JAN-2012 00:00:00
      101 05-JAN-2012 00:00:00
      102 01-JAN-2012 00:00:00
      102 03-JAN-2012 00:00:00
6 rows selected.

　　然后用前面的集合减去后面的：

SELECT ei.empid, gd.date_val
FROM emp_info ei
CROSS JOIN
TABLE(generate_dates(DATE '2012-01-01', DATE '2012-01-05')) gd
MINUS
SELECT ei.empid, ea.emp_day
FROM emp_info ei
INNER JOIN emp_attendance ea
ON ea.empid = ei.empid
WHERE ea.emp_day BETWEEN DATE '2012-01-01' AND DATE '2012-01-05'
ORDER BY 1, 2;
    EMPID DATE_VAL
---------- --------------------
      101 03-JAN-2012 00:00:00
      102 02-JAN-2012 00:00:00
      102 04-JAN-2012 00:00:00
      102 05-JAN-2012 00:00:00

　　注意empid 101在你提到的日期内缺席了，empid 102不仅缺席了2号，还缺席了4号和五号。所以这一点非常重要，就是你一定要确保所有的集合都是在一个时间表之内来进行比较的。

　　如果你想要它们的真是名称，你可以把这些放到一个内联视图中，然后再连接回emp_info表。

SELECT i.empid, i.empname, a.absent
FROM emp_info i
INNER JOIN
 (SELECT ei.empid, gd.date_val absent
  FROM emp_info ei
  CROSS JOIN
  TABLE(generate_dates(DATE '2012-01-01', DATE '2012-01-05')) gd
  MINUS
  SELECT ei.empid, ea.emp_day
  FROM emp_info ei
  INNER JOIN emp_attendance ea
  ON ea.empid = ei.empid
  WHERE ea.emp_day BETWEEN DATE '2012-01-01' AND DATE '2012-01-05') a
ON a.empid = i.empid
ORDER BY i.empid, a.absent;
    EMPID EMPNAME                                            ABSENT
---------- -------------------------------------------------- --------------------
      101 Dan Clamage                                        03-JAN-2012 00:00:00
      102 Ronak Khandelwal                                   02-JAN-2012 00:00:00
      102 Ronak Khandelwal                                   04-JAN-2012 00:00:00
      102 Ronak Khandelwal                                   05-JAN-2012 00:00:00

作者

Dan Clamage
Dan Clamage

相关推荐