Question

oracle中用sql语句计算时间间隔并更新表

oracle数据库中，将同一个人出行的时间间隔（天数）计算出来并更新时间间隔字段内容，出行日期都是排序好的，不用考虑排序问题，但是出行日期可能会有重复，该表还存在其他字段每行记录不重复

Answer 1

--最终查询语句：
select t.*,
(select decode(t1.出行日期,null,'',to_date(t.出行日期,'yyyy-mm-dd')-to_date(t1.出行日期,'yyyy-mm-dd')) from (
select x.lev, x.姓名, to_char(出行日期, 'yyyy-mm-dd') 出行日期
  from (SELECT ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 出行日期 ) LEV,
               r.*
          FROM 数据表名 r) x) t1 where t1.姓名=t.姓名 and t1.lev=(t.lev-1)) 时间间隔
 from(
select x.lev, x.姓名, to_char(出行日期, 'yyyy-mm-dd') 出行日期
  from (SELECT ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 出行日期 ) LEV,
               r.*
          FROM 数据表名 r) x) t 
          order by 姓名,出行日期

思路：先查询表中根据姓名和出行日期进行排序然后按照姓名和出行日期的顺序给表虚拟一个序列，比如张三 1  2  3  4  5  李四 1  2  3  4 ，使用语句如下：

select x.lev, x.姓名, to_char(出行日期, 'yyyy-mm-dd') 出行日期
  from (SELECT ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 出行日期 DESC) LEV,
               r.*
          FROM 数据表名 r) x

接下来就是查询这个表数据，然后再查询相同的人的上一个序号的数据，比如当前一条数据是姓名：张三，序号：3的数据，那么我们就需要查询张三、序号2的数据然后时间相减即可。

注意：数据表中出行日期字段是date类型的数据

Answer 2

可以select ROW_NUMBER() OVER(order by date) as row_num,* from table_name；
针对上述查询条件写一个loop， 初始i=1，j=2；每次循环加1，循环次数=行数-1
每次update row_num为j行，值为j行日期-i行日期

Answer 3

为了更多有此需求的同学看到想要的答案：

create table agile_test(
name_id varchar2(32),
date_go date,
date_no number(8,3)
);


INSERT INTO AGILE_TEST (NAME_ID, DATE_GO, DATE_NO) VALUES ( '张三', to_date('2019-01-01','yyyy-mm-dd'), 0 );
INSERT INTO AGILE_TEST (NAME_ID, DATE_GO, DATE_NO) VALUES ( '张三', to_date('2019-01-03','yyyy-mm-dd'), 0 );
INSERT INTO AGILE_TEST (NAME_ID, DATE_GO, DATE_NO) VALUES ( '张三', to_date('2019-01-07','yyyy-mm-dd'), 0 );
INSERT INTO AGILE_TEST (NAME_ID, DATE_GO, DATE_NO) VALUES ( '张三', to_date('2019-01-10','yyyy-mm-dd'), 0 );
COMMIT;


BEGIN
  FOR RCD IN (
    select ROWID RID,t.*, DATE_GO - NVL(lag(DATE_GO) over(partition by T.NAME_ID order by T.DATE_GO),DATE_GO)  DATE_NO_COUNT from AGILE_TEST t 
  ) LOOP
    UPDATE AGILE_TEST T SET T.DATE_NO = RCD.DATE_NO_COUNT
    WHERE T.ROWID = RCD.RID;
  END LOOP;
END;
/

因为select计算用到开窗函数所以不能直接update需要LOOP或子查询处理。

Answer 4

select count(*)
from (select 人员编号,
sum(case
when 变动类型 = '新增' then
1
else
0
end) as 新增次数,
sum(case
when 变动类型 = '删除' then
1
else
0
end) as 删除次数
from ceshi
group by 人员编号)
where 新增次数 - 删除次数 = 1;
select max(时间), 人员编号
from ceshi
where 人员编号 in (select 人员编号
from (select 人员编号,
sum(case
when 变动类型 = '新增' then
1
else
0
end) as 新增次数,
sum(case
when 变动类型 = '删除' then
1
else
0
end) as 删除次数
from ceshi
group by 人员编号)
where 新增次数 - 删除次数 = 1)
group by 人员编号;