求一个oracle的语句算出时效特别急

Question

数据如图，现在有个需求是如下：
首先我根据业务号：800120091026614查询出的数据如图，这是一个整个业务流程的过程，FROM_STS字段是某一个业务的开始节点，用数字表示的，TO_STS是某个业务的结束节点，DEAL_TIME是处理的时间，这个表的主键是ID。
现在要实现的效果是某一个流程处理完后的时效，时效是用DEAL_TIME字段进行相减来得到的，算到天就行。
比如：81209的结束节点对应的开始节点为FROM_STS字段中对应的81209字段，但是很有意思的是对应的这开始节点中的81209有4个，从肉眼上看是取数据为第二条的那个81209的时间-第一条中结束节点为81209的时间，但是数据库中怎么让结束节点找到对应的开始节点（两个时间点最近的肯定是对的），并且怎么让数据库只减一次，比如81209对应的开始节点有4个，我怎么减我那个最近的开始节点的时间。
想了好久了，求大侠帮忙。
select b.*,TRUNC(B.Deal_Time, 'dd') - trunc(A.Deal_Time, 'dd')  
  from TMS_BUSI_FLOW_STS_LOG A, TMS_BUSI_FLOW_STS_LOG B
 where A.APPLY_ID = B.Apply_Id
   and A.To_Sts = B.From_Sts
   and A.apply_id = '800120091026614'
这是我写的sql，但是遇到结束节点对应多个开始节点的时候就有问题了。我这个sql是一个一个减的，所以遇到像81209就会减4次，并且这4次中我不知道我要哪一次。求教oracle的困惑

Answer 1

还是比较混乱 to里面对应的81209也不少 怎么就知道要对面from里面第二个而不是第四个呢最好上一点测试数据 和结果 看的直观

Answer 2

其实按照道理来说就是TO里面的数据对应FROM里面数据只能是一一对应的，因为FROM里面有4个所以TO里面也是4个，但是我也没法找到TO中的81209对应的是哪个FROM中的81209,真的只能从肉眼中看出TO中的81209对应的时间和FROM中81209对应的时间是最近的就是那个，数据真心的没有，这个表中的数据都是从生产上看的，具体的业务就是某个人要完成一个业务，但是提交到上级审核后，上级觉得有问题然后打回了，然后此人又重新来了遍这个业务，所以会出现多个相同的FROM和多个相同的TO

Answer 3

数据里面也没有回退的标识，可以试下用 ROW_NUMBER() OVER(ORDER BY FROM_STS)
你弄个模拟测试数据出来下,这样才好玩下。。

Answer 4

这个库库结构设计的有点小问题，起码要设计个键值对 来对应你的需求。既然你肉眼的条件就是最近的那条记录，那你就取最近的哪条数据不就ok了。eg： 你的 TO里面的数据 2013-10-26 18:07:24 那你就取 from 2019 时间大于 2013-10-26 18:07:24  升序取第一条呗，这样不就ok了。

Answer 5

create table TMS_BUSI_FLOW_STS_LOG
(
  APPLY_ID          VARCHAR2(40),
  FROM_STS          NUMBER(10) not null,
  TO_STS            NUMBER(10) not null,
  DEAL_DESC         VARCHAR2(4000),
  OPER_ID           NUMBER(10),
  DEAL_TIME         DATE,
  ID                NUMBER(10) not null,
  COLLECT_BILL_DATE DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table TMS_BUSI_FLOW_STS_LOG
  is '业务状态变更明细表';
-- Add comments to the columns 
comment on column TMS_BUSI_FLOW_STS_LOG.APPLY_ID
  is '申请编码';
comment on column TMS_BUSI_FLOW_STS_LOG.FROM_STS
  is '变更前状态';
comment on column TMS_BUSI_FLOW_STS_LOG.TO_STS
  is '变更后状态';
comment on column TMS_BUSI_FLOW_STS_LOG.DEAL_DESC
  is '受理说明';
comment on column TMS_BUSI_FLOW_STS_LOG.OPER_ID
  is '受理人';
comment on column TMS_BUSI_FLOW_STS_LOG.DEAL_TIME
  is '受理时间';
comment on column TMS_BUSI_FLOW_STS_LOG.ID
  is '记录ID';
comment on column TMS_BUSI_FLOW_STS_LOG.COLLECT_BILL_DATE
  is '签单日期';
insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81208, 81209, '用户登录机器IP地址:10.17.9.116,访问功能为:/sub.contract.busi.sts.change', 115881, to_date('26-10-2009 18:07:24', 'dd-mm-yyyy hh24:mi:ss'), 27406, null);insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81209, 81208, '<br>用户登录机器IP地址:10.46.10.16,访问功能为:/baseinfo.approve', 154766, to_date('27-10-2009 09:25:05', 'dd-mm-yyyy hh24:mi:ss'), 27410, to_date('27-10-2009', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81208, 81209, '用户登录机器IP地址:10.17.9.116,访问功能为:/sub.contract.busi.sts.change', 115881, to_date('27-10-2009 09:38:31', 'dd-mm-yyyy hh24:mi:ss'), 27412, null);insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81209, 81219, '<br>用户登录机器IP地址:10.46.10.16,访问功能为:/baseinfo.approve', 154766, to_date('27-10-2009 09:42:55', 'dd-mm-yyyy hh24:mi:ss'), 27414, to_date('27-10-2009', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81219, 81229, '<br>用户登录机器IP地址:10.9.19.119,访问功能为:/contract.print', 362614, to_date('16-08-2011 16:37:56', 'dd-mm-yyyy hh24:mi:ss'), 138692, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81229, 81220, '用户登录机器IP地址:10.9.19.117,访问功能为:/sub.contract.busi.sts.change', 362614, to_date('16-08-2011 16:40:04', 'dd-mm-yyyy hh24:mi:ss'), 138699, null);insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81331, 81225, '<br>用户登录机器IP地址:10.9.19.117,访问功能为:/contract.intput.upload.submit', 362614, to_date('16-08-2011 17:00:33', 'dd-mm-yyyy hh24:mi:ss'), 138746, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81225, 81226, '<br>用户登录机器IP地址:10.9.19.119,访问功能为:/generate.contract.record.confirm', 362614, to_date('16-08-2011 17:01:24', 'dd-mm-yyyy hh24:mi:ss'), 138751, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81226, 81227, '<br>用户登录机器IP地址:10.9.19.119,访问功能为:/contract.record.letter.submit', 362614, to_date('16-08-2011 17:01:35', 'dd-mm-yyyy hh24:mi:ss'), 138752, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81222, 81331, '<br>用户登录机器IP地址:10.9.19.119,访问功能为:/contract.print', 362614, to_date('16-08-2011 16:53:54', 'dd-mm-yyyy hh24:mi:ss'), 138731, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81209, 81219, '<br>用户登录机器IP地址:10.9.19.119,访问功能为:/baseinfo.approve', 362614, to_date('16-08-2011 16:20:03', 'dd-mm-yyyy hh24:mi:ss'), 138666, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81209, 81208, '1、其他:账户管理费支付周期。<br>用户登录机器IP地址:10.9.19.119,访问功能为:/baseinfo.approve', 362614, to_date('16-08-2011 16:18:13', 'dd-mm-yyyy hh24:mi:ss'), 138658, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81208, 81209, '用户登录机器IP地址:10.9.19.117,访问功能为:/sub.contract.busi.sts.change', 362614, to_date('16-08-2011 16:18:56', 'dd-mm-yyyy hh24:mi:ss'), 138660, null);insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81208, 81209, '用户登录机器IP地址:10.9.19.117,访问功能为:/sub.contract.busi.sts.change', 362614, to_date('16-08-2011 16:12:14', 'dd-mm-yyyy hh24:mi:ss'), 138653, null);insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81220, 81222, '<br>用户登录机器IP地址:10.9.19.119,访问功能为:/baseinfo.approve', 362614, to_date('16-08-2011 16:41:53', 'dd-mm-yyyy hh24:mi:ss'), 138705, to_date('16-08-2011', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81228, 9999, '<br>用户登录机器IP地址:127.0.0.1,访问功能为:/contract.effective.submit', 1, to_date('13-03-2012 16:08:12', 'dd-mm-yyyy hh24:mi:ss'), 147519, to_date('13-03-2012', 'dd-mm-yyyy'));insert into tms_busi_flow_sts_log (APPLY_ID, FROM_STS, TO_STS, DEAL_DESC, OPER_ID, DEAL_TIME, ID, COLLECT_BILL_DATE)
values ('800120091026614', 81227, 81228, '<br>用户登录机器IP地址:10.50.7.219,访问功能为:/planconfirmation.upload', 1, to_date('14-02-2012 13:42:28', 'dd-mm-yyyy hh24:mi:ss'), 143557, to_date('14-02-2012', 'dd-mm-yyyy'));测试数据，供大家用的，谢谢

Answer 6

就像5楼说的，你说是最近的那条记录，那时间差最小的那个不就是你要的么select t.*, floor(t.timeRange) from (
  select b.*, B.Deal_Time-A.DealTime  timeRange
    from TMS_BUSI_FLOW_STS_LOG A, TMS_BUSI_FLOW_STS_LOG B
  where A.APPLY_ID = B.Apply_Id
    and A.To_Sts = B.From_Sts
    and A.apply_id = '800120091026614'
    and B.Deal_Time>A.Deal_Time
    and order by (B.Deal_Time-A.DealTime)
) t where rownum=1不过我觉得这样还是不对，你这要是中间某个人老长时间没有处理发出的那条申请，这个时间差反而很大，你这样算的话不就错了么

Answer 7

不好意思写错了，order by前多了个andselect t.*, floor(t.timeRange) from (
  select b.*, B.Deal_Time-A.DealTime  timeRange
    from TMS_BUSI_FLOW_STS_LOG A, TMS_BUSI_FLOW_STS_LOG B
  where A.APPLY_ID = B.Apply_Id
    and A.To_Sts = B.From_Sts
    and A.apply_id = '800120091026614'
    and B.Deal_Time>A.Deal_Time
    order by (B.Deal_Time-A.DealTime)
) t where rownum=1

Answer 8

昨天想到一个没有办法中的办法，就是不知道是否有高人有更好的办法。我的想法如下：--1、删除表
drop  table  xx;
--2、创建数据表
create table  xx( apply_id varchar2(40) ,id1 number(10),deal_time1 date,id2 number(10),deal_time2 date,cost_time float) ;
--3、向数据表中插入数据
insert into xx
  (select y.apply_id, y.id, y.deal_time, '', '', ''
     from tms_busi_flow_sts_log y
    where y.id in
          (select tl.id
             from tms_busi_flow_sts_log tl
            where tl.apply_id ='800120091026614'
                  ));
--4、查询插入数据
select * from xx;
--5、
update  xx set xx.id2=(select max(c.id) from tms_busi_flow_sts_log c where c.apply_id=xx.apply_id and c.id<xx.id1);
--6、
select * from xx order by xx.deal_time1;
--7、
update  xx set xx.deal_time2=(select deal_time from tms_busi_flow_sts_log e where e.apply_id=xx.apply_id and e.id=xx.id2);
--8、
update  xx set xx.deal_time2=(select h.fcd from tms_busi_apply h where h.apply_id=xx.apply_id) where xx.id2 is null;
--9、
update  xx set xx.cost_time=xx.deal_time1-xx.deal_time2;
--10、
select * from xx order by xx.deal_time1;
select count(*) from xx order by xx.deal_time1;