2 wei516980245 wei516980245 于 2016.03.14 11:15 提问

求一日期匹配的ORACEL sql。在线等!!!

ZT KSSJ JSSJ MBID
ZF 2015-12-11 2016-12-11 1
ZF 2015-12-18 2016-12-18 1
ZF 2015-12-18 2016-12-18 1
ZF 2015-12-19 2016-12-19 1
TF 2016-01-14 1
ZF 2016-02-19 2017-02-19 1
ZF 2016-02-29 2016-04-30 1
TF 2016-03-21 1
ZF 2015-12-11 2016-12-11 3
ZF 2015-12-18 2016-12-18 3
ZF 2015-12-18 2016-12-18 3
ZF 2015-12-19 2016-12-19 3
TF 2016-01-14 3
ZF 2016-02-19 2017-02-19 3
ZF 2016-02-19 2017-02-19 3
ZF 2016-01-14 2016-03-14 4
ZF 2016-02-04 2017-02-04 4
ZF 2016-02-19 2017-02-19 4
TF 2016-02-29 4
ZF 2016-02-19 2017-02-19 61

上面为原始数据。最终想要的结果为:
KSSJ JSSJ MBID
2015-12-11 2016-01-14 1
2016-02-19 2016-03-21 1
2015-12-11 2016-01-14 3
2016-02-19 2017-02-19 3
2015-01-14 2016-02-29 4
2016-02-19 2017-02-19 61

2个回答

devmiao
devmiao   Ds   Rxr 2016.03.14 12:27
danielinbiti
danielinbiti   Ds   Rxr 2016.03.14 15:26
 create table TEST_C(
  ZT VARCHAR(50),
  KSSJ VARCHAR(50),
   JSSJ VARCHAR(50), 
   MBID  VARCHAR(50)
);
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-11','2016-12-11','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-18','2016-12-18','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-18','2016-12-18','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-19','2016-12-19','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('TF','2016-01-14','','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-19','2017-02-19','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-29','2016-04-30','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('TF','2016-03-21','','1');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-11','2016-12-11','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-18','2016-12-18','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-18','2016-12-18','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2015-12-19','2016-12-19','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('TF','2016-01-14','','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-19','2017-02-19','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-19','2017-02-19','3');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-01-14','2016-03-14','4');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-04','2017-02-04','4');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-19','2017-02-19','4');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('TF','2016-02-29','','4');
INSERT INTO TEST_C(ZT,KSSJ,JSSJ,MBID) VALUES('ZF','2016-02-19','2017-02-19','61');

select min(Rkssj) s1,min(kssj) s2 ,RMBID
from(
SELECT C.*,R.kssj rkssj,R.MBID RMBID
FROM (
  SELECT C.*, lag(rn,1,0) over ( order by RN ) PRERN from (
  select * from (
         SELECT ROWNUM RN,C.* FROM TEST_C C
  ) WHERE ZT='TF') C
) C
,
(SELECT ROWNUM RNINNER,C.* FROM TEST_C C) R
where RNINNER<C.RN AND  RNINNER>C.PRERN 
ORDER BY R.RNINNER
) group by rn,RMBID ORDER BY RN
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
oracel总结
u从一个用户转到令一个用户:conn  请输入用户名:scott/tiger给scott用户解锁:alter user scott account unlock;sqlplus sys/bjsxt as sysdba  系统管理员的密码是:bjsxt一、oracle需要记住的:分页用:rownum       修改表结构和添加约束条件用:alter table       索引:是为了加快对数
Oracel SQL tuning
Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning
ORACEL常用日期操作
1.日期时间间隔操作 当前时间减去7分钟的时间 select  sysdate,sysdate - interval 7 MINUTE  from dual 当前时间减去7小时的时间 select  sysdate - interval 7 hour  from dual 当前时间减去7天的时间 select  sysdate - interval 7 day  from dual 当
Oracel给字段设置索引
create index idx_t(索引名) on t(object_id) ; idx_t:索引名 t:表名 object_id:需要建立索引的字段
ORACEL 日期处理
TO_DATE格式 Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out march Year: yy two digits 98 yyyy 
如何将Oracle 当前日期加一天、一分钟?
在Oralce中我发现有add_months函数,加天数N可以用如下方法实现,select sysdate+N from dual ,sysdate+1 加一天sysdate+1/24 加1小时sysdate+1/(24*60) 加1分钟sysdate+1/(24*60*60) 加1秒钟类推至毫秒0.001秒
oracle计算两个日期之间时间差值sql
sql Server中有函数DATEDIFF能够比较方便的计算出两个时间点之间的时间差值,但是oracle就没有,需要自己写方法计算,这是比较坑爹的。 自己写了个分享一下 create or replace function datediff(type in varchar2, startTime in varchar2,endTime in varchar2,v in varchar2) r
创建一个表循环插入某个月或整年的日期
--写查询或存储过程时,有时可能会需要创建一个中间表,比如日期,下面是循环插入当月所有日期和整年所有日期的SQL --创建会话级临时表用来存储日期,不过我这里日期存储的是varchar2 类型 CREATE GLOBAL TEMPORARY TABLE RQ(ID NUMBER PRIMARY KEY ,SJ VARCHAR2(20)) ON COMMIT PRESERVE ROWS SELE
Aceess中插入时间提示标准数据类型不匹配
个人使用软件插入时间到Access数据库中,提示“标准数据类型不匹配”。我在XP,win8.1和server2008系统上都可以正常运行,但是一到win10上就报错了。网上查了不少资料也解决不了。偶然看到一个提问,“在Access中创建表时出生日期用长日期时后面带星期几怎么去掉!”。忽然想到,在win10 系统插入时多了个星期几,即日期格式变成了yyyy/MM/dd  dddd,然后改为yyyy/
Oracle 计算两个日期的时间差
select count(*) from table_a where ROUND(TO_NUMBER(END_DATE - START_DATE) * 24) > 6 天:ROUND(TO_NUMBER(END_DATE - START_DATE)) 小时:ROUND(TO_NUMBER(END_DATE - START_DATE) * 24) 分钟:ROUND(TO_NUMBER(