oracle怎么根据时间字段对新字段设置个值

ajdkjalj 2018-11-07 10:57:33

我想根据时间段给新字段设值，比如8:30-9:30的，给新字段设为8,9:30-10:30的设为9，依次类推，也就是当时间字段A的值为2018-11-07 08:45：00的，新字段B赋值为8，A值为2018-11-07 20:15：00，B赋值为19

...全文

182 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

卖水果的net 2018-11-07

打赏
举报


-- 除了 1 # 之外的触发器之外，也可以考虑计算列

SQL> create table test(
  2    id int,
  3    crdate date,
  4    colx varchar(10) as (to_char(crdate + 1 / 24 / 2, 'hh24'))
  5  );

表已创建。

SQL>
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> col id format 99999;
SQL> set pagesize 1000;
SQL>
SQL> insert into test(id, crdate)
  2  select rownum, sysdate + rownum / 48 from dual connect by rownum <=48;

已创建 48 行。

SQL>
SQL> select id, crdate,  colx from test;

    ID CRDATE              COLX
------ ------------------- --------------------
     1 2018-11-07 12:13:07 12
     2 2018-11-07 12:43:07 13
     3 2018-11-07 13:13:07 13
     4 2018-11-07 13:43:07 14
     5 2018-11-07 14:13:07 14
     6 2018-11-07 14:43:07 15
     7 2018-11-07 15:13:07 15
     8 2018-11-07 15:43:07 16
     9 2018-11-07 16:13:07 16
    10 2018-11-07 16:43:07 17
    11 2018-11-07 17:13:07 17
    12 2018-11-07 17:43:07 18
    13 2018-11-07 18:13:07 18
    14 2018-11-07 18:43:07 19
    15 2018-11-07 19:13:07 19
    16 2018-11-07 19:43:07 20
    17 2018-11-07 20:13:07 20
    18 2018-11-07 20:43:07 21
    19 2018-11-07 21:13:07 21
    20 2018-11-07 21:43:07 22
    21 2018-11-07 22:13:07 22
    22 2018-11-07 22:43:07 23
    23 2018-11-07 23:13:07 23
    24 2018-11-07 23:43:07 00
    25 2018-11-08 00:13:07 00
    26 2018-11-08 00:43:07 01
    27 2018-11-08 01:13:07 01
    28 2018-11-08 01:43:07 02
    29 2018-11-08 02:13:07 02
    30 2018-11-08 02:43:07 03
    31 2018-11-08 03:13:07 03
    32 2018-11-08 03:43:07 04
    33 2018-11-08 04:13:07 04
    34 2018-11-08 04:43:07 05
    35 2018-11-08 05:13:07 05
    36 2018-11-08 05:43:07 06
    37 2018-11-08 06:13:07 06
    38 2018-11-08 06:43:07 07
    39 2018-11-08 07:13:07 07
    40 2018-11-08 07:43:07 08
    41 2018-11-08 08:13:07 08
    42 2018-11-08 08:43:07 09
    43 2018-11-08 09:13:07 09
    44 2018-11-08 09:43:07 10
    45 2018-11-08 10:13:07 10
    46 2018-11-08 10:43:07 11
    47 2018-11-08 11:13:07 11
    48 2018-11-08 11:43:07 12

已选择48行。

SQL>
SQL> drop table test purge;

表已删除。

SQL>

Wazy_csdn 2018-11-07