问题描述
- 在线等 有关对(lag函数)的使用或者其他的函数可以达到目的的也可以
- 在一张日历表中有一个字段名称为ISOM只存在两个数值,0的意思代表不是交易日,1的意思代表交易,另外有一个字段TIME就是按顺序来的日期,怎样凑出第三个字段C这个字段的意思是,如果ISOM为0,则要取上一个最近的标为1的日期,如果为1,就取当天的日期
(在ORACLE里面执行)
解决方案
发让人让人人反反复复
解决方案二:
create table test_date( t_TIME varchar(20) t_ISOM number default 0)DELETE FROM test_date;insert into test_date(t_TIMEt_ISOM) values('20140101'1);insert into test_date(t_TIMEt_ISOM) values('20140102'0);insert into test_date(t_TIMEt_ISOM) values('20140103'0);insert into test_date(t_TIMEt_ISOM) values('20140104'0);insert into test_date(t_TIMEt_ISOM) values('20140105'0);insert into test_date(t_TIMEt_ISOM) values('20140106'0);insert into test_date(t_TIMEt_ISOM) values('20140107'0);insert into test_date(t_TIMEt_ISOM) values('20140108'1);insert into test_date(t_TIMEt_ISOM) values('20140109'1);insert into test_date(t_TIMEt_ISOM) values('20140110'0);insert into test_date(t_TIMEt_ISOM) values('20140111'1);insert into test_date(t_TIMEt_ISOM) values('20140112'0);insert into test_date(t_TIMEt_ISOM) values('20140113'0);insert into test_date(t_TIMEt_ISOM) values('20140114'1);
select
case when T_ISOM=1 THEN T_TIME
ELSE
N
END RESULT_TIME --要的结果
C.*
FROM
(
SELECT B.*(M-B.T_TIME) AS M_D(B.T_TIME-N) AS N_D FROM
(
SELECT A.*MAX(DNEXT) OVER(PARTITION BY x) AS MMIN(DPRE) OVER(PARTITION BY x) AS N FROM
(
SELECT t_TIMEt_ISOMROW_NUMBER() OVER(ORDER BY t_TIME)-ROW_NUMBER() OVER(PARTITION BY t_ISOM ORDER BY t_TIME) x
Lead(t_TIME) over(order by t_TIME) as dnextlag(t_TIME) over(order by t_TIME) as dpre
FROM test_date
) A order by t_time
) B
) c order by t_time
>
时间: 2025-01-05 20:57:56