17,086
社区成员
发帖
与我相关
我的任务
分享
select b.* from t b
where (b.product,b.date2) in
(
select distinct a.product,
MAX(a.date2) KEEP (DENSE_RANK first ORDER BY a.date1) OVER (PARTITION BY a.product) rn
from T A
WHERE a.DATE1>a.DATE2
)
这样也行,但是没有 #3 性能好with tmp as
(select date '2016-1-1' as date1,'AAA' as str, date '2015-7-1' as date2,'产品1' as product from dual union all
select date '2016-1-1' ,'BBB' , date '2015-9-10' ,'产品1' from dual union all
select date '2016-1-1' ,'CCC' , date '2016-5-5' ,'产品1' from dual union all
select date '2016-1-1' ,'D' , date '2015-6-23' ,'产品2' from dual union all
select date '2016-1-1' ,'E' , date '2016-1-8' ,'产品2' from dual union all
select date '2016-1-1' ,'F' , date '2016-7-1' ,'产品2' from dual union all
select date '2016-1-1' ,'G' , date '2015-7-16' ,'产品3' from dual union all
select date '2016-1-1' ,'H' , date '2015-9-10' ,'产品3' from dual union all
select date '2016-1-1' ,'I' , date '2015-10-20' ,'产品3' from dual)
SELECT DATE1,STR,DATE2,PRODUCT FROM
(SELECT T.*,NVL(LEAD(DATE2)OVER(PARTITION BY T.PRODUCT ORDER BY DATE2),TO_DATE('9999-12-31','YYYY-MM-DD')) AS NEXT_DATE2 FROM TMP T)
WHERE DATE1 BETWEEN DATE2 AND NEXT_DATE2
with t as(
select '2016-01-01' as date1,'AAA' as str,'2015-07-01' as date2,'产品1' as product from dual
union all
select '2016-01-01' as date1,'BBB' as str,'2015-09-10' as date2,'产品1' as product from dual
union all
select '2016-01-01' as date1,'CCC' as str,'2016-05-05' as date2,'产品1' as product from dual
union all
select '2016-01-01' as date1,'D' as str,'2015-06-23' as date2,'产品2' as product from dual
union all
select '2016-01-01' as date1,'E' as str,'2016-01-08' as date2,'产品2' as product from dual
union all
select '2016-01-01' as date1,'F' as str,'2016-07-01' as date2,'产品2' as product from dual
union all
select '2016-01-01' as date1,'G' as str,'2015-07-16' as date2,'产品3' as product from dual
union all
select '2016-01-01' as date1,'H' as str,'2015-09-10' as date2,'产品3' as product from dual
union all
select '2016-01-01' as date1,'I' as str,'2015-10-20' as date2,'产品3' as product from dual
)
select * from (
select a.str,a.product,a.date2,DENSE_RANK() OVER (PARTITION BY a.product ORDER BY date2 DESC) RN
from T A
WHERE a.DATE1>a.DATE2
)
where rn=1
select * from (
select date1,
str,
date2,
product,
row_number() over(partition by product order by date2 desc) as rn
from (select * from Test1 t where t.date1>t.date2) )
where rn = 1;
当然,您在5楼的回复也可以解决这个问题。不过 你写的这个分析函数看不太懂。。