主页 / dba / 问题 / 149930
Accepted
DecoderReloaded
Asked: 2016-09-17 22:37:38 +0800 CST

如何从 status_track 表中获取状态 start_date 和 end_date？

  • 772

我有两张桌子：

  1. things:

    +------+----------------+------------+
| id   | current_status | created_at |
+------+----------------+------------+
| 7770 | active         | 2016-08-09 |
+------+----------------+------------+

  2. thing_status_tract:

    +----------+-------------+-----------+---------------------+
| thing_id | status_from | status_to | changed_at          |
+----------+-------------+-----------+---------------------+
|     7770 | incomplete  | inactive  | 2016-08-09 16:26:22 |
|     7770 | inactive    | active    | 2016-08-10 12:31:04 |
+----------+-------------+-----------+---------------------+

我需要以下形式的数据。此表具有特定状态及其相应的开始和结束时间戳thing_id：

+----------+-------------+---------------------+---------------------+
| thing_id | status      | status_start_date   | status_end_date     |
+----------+-------------+---------------------+---------------------+
|     7770 | incomplete  | 2016-08-09 00:00:00 | 2016-08-09 16:26:22 |
|     7770 | inactive    | 2016-08-09 16:26:22 | 2016-08-10 12:31:04 |
|     7770 | active      | 2016-08-10 12:31:04 | now()               |
+----------+-------------+---------------------+---------------------+

如何使用 SQL 查询来做到这一点？

mysql oracle

2 个回答

  1. Best Answer

    当然，我之前的回答只是为了回答你眼前的问题。正如我在评论中提到的，您的数据模型本身存在缺陷。你不仅不应该在历史表中同时保留 'status_from' (prevous_status) 和 'status_to'，你也不应该在 THINGS 表中保留状态。这违反了标准数据规范化规则。

    您应该做的是从 THINGS 表中删除状态，在两个表之间建立 FK 关系，并在需要时使用适当的查询来导出当前状态。

    首先，创建并填充 THINGS 表。我添加了几列只是为了说明该表中应包含的内容：

    小号

    QL> create table things (thing_id number,
  2                       attrib_a varchar2(10),
  3                       attrib_b varchar2(10),
  4                         constraint thing_pk primary key (thing_id)
  5                      )
  6  ;

Table created.

SQL> insert into things
  2     values (7770,
  3             'red',
  4             'white'
  5            )
  6  ;

1 row created.

SQL> insert into things
  2     values (8880,
  3             'blue',
  4             'green'
  5            )
  6  ;

1 row created.

    然后创建并填充历史跟踪表：

    SQL> create table thing_status
  2       (thing_id number,
  3        new_status  varchar2(10),
  4        status_date date,
  5          constraint fk_things
  6            foreign key (thing_id)
  7            references things(thing_id)
  8        )
  9  ;

Table created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (7770,
  3             'incomplete',
  4             to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'inactive',
  4             to_date('2016-08-10 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'active',
  4             to_date('2016-08-11 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (8880,
  3             'incomplete',
  4             to_date('2016-08-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'inactive',
  4             to_date('2016-08-13 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'active',
  4             to_date('2016-08-14 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'expired',
  4             to_date('2016-08-15 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

    还有你的历史报告（和以前一样）

    SQL> -- -----------------------------------------------------
SQL> --  Select status history
SQL> --
SQL> select thing_id,
  2         new_status,
  3         to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4         lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5           over (partition by thing_id order by status_date) status_end_date
  6  from thing_status
  7  order by thing_id,
  8           status_date
  9  ;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-10 16:26:22
      7770 inactive   10-Aug-2016 16:26:22 2016-08-11 12:32:04
      7770 active     11-Aug-2016 12:32:04
      8880 incomplete 12-Aug-2016 00:00:00 2016-08-13 16:26:22
      8880 inactive   13-Aug-2016 16:26:22 2016-08-14 12:32:04
      8880 active     14-Aug-2016 12:32:04 2016-08-15 12:32:04
      8880 expired    15-Aug-2016 12:32:04

7 rows selected.

    还有你的现状报告

    SQL> -- -----------------------------------------------------
SQL> --  Select current status
SQL> --
SQL> select t.thing_id,
  2         t.attrib_a,
  3         t.attrib_b,
  4         s.new_status curr_status,
  5         to_char(s.status_date,'yyyy-mm-dd hh24:mi:ss') status_date
  6  from things t
  7   join thing_status s
  8     on t.thing_id = s.thing_id
  9  where s.status_date = (select max(status_date)
 10                         from thing_status x
 11                         where s.thing_id = x.thing_id
 12                         group by thing_id
 13                        )
 14  order by t.thing_id
 15  ;

  THING_ID ATTRIB_A   ATTRIB_B   CURR_STATU STATUS_DATE
---------- ---------- ---------- ---------- -------------------
      7770 red        white      active     2016-08-11 12:32:04
      8880 blue       green      expired    2016-08-15 12:32:04
    • 4 
  2. SQL> create table thing_status_tract (thing_id number,
  2        new_status  varchar2(10),
  3        status_date date
  4       )
  5  ;

Table created.

SQL> insert into thing_status_tract
  2  values (7770,
  3  'incomplete',
  4  to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (7770,
  3  'inactive',
  4  to_date('2016-08-09 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (7770,
  3  'active',
  4  to_date('2016-08-10 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  3  'incomplete',
  4  to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  3  'inactive',
  4  to_date('2016-08-09 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> insert into thing_status_tract
  2  values (8880,
  3  'active',
  4  to_date('2016-08-10 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5  )
  6  ;

1 row created.

SQL> select thing_id,
  2      new_status,
  3      to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4      lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5        over (partition by thing_id order by status_date) status_end_date
  6  from thing_status_tract
  7  order by thing_id,
  8        status_date;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-09 16:26:22
      7770 inactive   09-Aug-2016 16:26:22 2016-08-10 12:32:04
      7770 active     10-Aug-2016 12:32:04
      8880 incomplete 09-Aug-2016 00:00:00 2016-08-09 16:26:22
      8880 inactive   09-Aug-2016 16:26:22 2016-08-10 12:32:04
      8880 active     10-Aug-2016 12:32:04

6 rows selected.
    • 0

相关问题