sql - 数据丢失时插入表

Question

我有一张表格，记录了不同日期的入站/出站国内/国际邮件。这是示例表:

Date          location       in_out_code      dom_int_code         mail_count
------------------------------------------------------------------------------
11/01/2012    chicago             in                I                  3
11/02/2012    la                  in                I                  2
11/03/2012    ny                  in                I                  4   
11/03/2012    ny                  out               D                  5
11/04/2012    phoenix             out               D                  1
11/05/2012    phoenix             in                D                  3

我想创建一个表来存储每天的所有组合，如下所示(为了便于阅读，我分解了输出):

Date         location          in_out_code     dom_int_code     mail_count
----------------------------------------------------------------------------
11/01/2012   chicago               in              I              3
11/01/2012   chicago               in              D              0 <-- inserted
11/01/2012   chicago               out             I              0 <-- inserted
11/01/2012   chicago               out             D              0 <-- inserted

11/02/2012   la                    in              I              2
11/02/2012   la                    in              D              0 <-- inserted
11/02/2012   la                    out             I              0 <-- inserted
11/02/2012   la                    out             D              0 <-- inserted

11/03/2012   ny                    in              I              4   
11/03/2012   ny                    in              D              0 <-- inserted
11/03/2012   ny                    out             I              0 <-- inserted
11/03/2012   ny                    out             D              5

11/04/2012   phoenix               in              I              0 <-- inserted
11/04/2012   phoenix               in              D              0 <-- inserted
11/04/2012   phoenix               out             I              0 <-- inserted
11/04/2012   phoenix               out             D              1

11/05/2012   phoenix               in              I              0 <-- inserted
11/05/2012   phoenix               in              D              3
11/05/2012   phoenix               out             I              0 <-- inserted
11/05/2012   phoenix               out             D              0 <-- inserted

我将如何做到这一点。有什么建议吗？

谢谢

Answer 1

Oracle 10g 及更高版本。您可以使用 partition by外连接的扩展以填补数据中的空白:

包含您提供的数据的表格:

SQL> create table Your_Table_Name(Date1,  location1, in_out_code, dom_int_code, mail_count) as(
  2    select to_date('11/01/2012', 'mm/dd/yyyy'),    'chicago',    'in',   'I',   3 from dual union all
  3    select to_date('11/02/2012', 'mm/dd/yyyy'),    'la'     ,    'in',   'I',   2 from dual union all
  4    select to_date('11/03/2012', 'mm/dd/yyyy'),    'ny'     ,    'in',   'I',   4 from dual union all
  5    select to_date('11/03/2012', 'mm/dd/yyyy'),    'ny'     ,    'out',  'D',   5 from dual union all
  6    select to_date('11/04/2012', 'mm/dd/yyyy'),    'phoenix',    'out',  'D',   1 from dual union all
  7    select to_date('11/05/2012', 'mm/dd/yyyy'),    'phoenix',    'in' ,  'D',   3 from dual
  8  )
  9  ;

Table created

我们的查询:

SQL> with t2(in_out_code, dom_int_code) as(
  2    select 'in',  'I' from dual union all
  3    select 'out', 'I' from dual union all
  4    select 'in',  'D' from dual union all
  5    select 'out', 'D' from dual
  6  )
  7  select t1.date1
  8       , t1.location1
  9       , t2.in_out_code
 10       , t2.dom_int_code
 11       , nvl(t1.mail_count, 0) as mail_count
 12    from your_table_name t1
 13    partition by (t1.date1, t1.location1)
 14    right outer join t2
 15      on (t1.in_out_code = t2.in_out_code and
 16          t1.dom_int_code = t2.dom_int_code)
 17  ;

结果:

DATE1       LOCATION1 IN_OUT_CODE DOM_INT_CODE MAIL_COUNT
----------- --------- ----------- ------------ ----------
11/01/2012   chicago   in          D                     0
11/01/2012   chicago   in          I                     3
11/01/2012   chicago   out         D                     0
11/01/2012   chicago   out         I                     0
11/02/2012   la        in          D                     0
11/02/2012   la        in          I                     2
11/02/2012   la        out         D                     0
11/02/2012   la        out         I                     0
11/03/2012   ny        in          D                     0
11/03/2012   ny        in          I                     4
11/03/2012   ny        out         D                     5
11/03/2012   ny        out         I                     0
11/04/2012   phoenix   in          D                     0
11/04/2012   phoenix   in          I                     0
11/04/2012   phoenix   out         D                     1
11/04/2012   phoenix   out         I                     0
11/05/2012   phoenix   in          D                     3
11/05/2012   phoenix   in          I                     0
11/05/2012   phoenix   out         D                     0
11/05/2012   phoenix   out         I                     0

20 rows selected