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币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!