在同一张表中,有两个字段begin_date和end_date,有多条记录但时间是不一样的,我有两个条件1、要取所有begin_date不相同并且end_date也不同的一系列数据2、符合1条件的同时还要sysdate-begin_date是最小的那条数据,我要的sql语句就是符合1,2条件的要求。请注意是一张表,请高手帮忙写这个语句,万分感谢!BEGIN_DATE END_DATE
----------- -----------
2009-6-3 2009-6-10
2009-6-7 2009-6-8
2009-6-3 2009-6-9
2009-6-4 2009-6-8
2009-6-9 2009-6-2
2009-6-7 2009-6-8
2009-6-7 2009-6-3
2009-6-2 2009-6-8
2009-6-6 2009-6-6
2009-6-4 2009-6-3
2009-6-1 2009-6-2
2009-6-3 2009-6-2
2009-6-4 2009-6-4
2009-6-8 2009-6-8
2009-6-2 2009-6-9
2009-6-7 2009-6-1
2009-6-9 2009-6-6
2009-6-1 2009-6-8
2009-6-9 2009-6-9
2009-6-6 2009-6-6
----------- -----------
2009-6-3 2009-6-10
2009-6-7 2009-6-8
2009-6-3 2009-6-9
2009-6-4 2009-6-8
2009-6-9 2009-6-2
2009-6-7 2009-6-8
2009-6-7 2009-6-3
2009-6-2 2009-6-8
2009-6-6 2009-6-6
2009-6-4 2009-6-3
2009-6-1 2009-6-2
2009-6-3 2009-6-2
2009-6-4 2009-6-4
2009-6-8 2009-6-8
2009-6-2 2009-6-9
2009-6-7 2009-6-1
2009-6-9 2009-6-6
2009-6-1 2009-6-8
2009-6-9 2009-6-9
2009-6-6 2009-6-6
end_date,
form 你的表
group by begin_date,end_date
from(
select begin_date,end_date,count(*) over(partition by begin_date) r1,
count(*) over(partition by end_date) r2,sysdate-begin_date mindate
from t_24)
where r1=1 and r2=1
group by begin_date,end_date
from(
select begin_date,end_date,count(*) over(partition by begin_date,end_date) r1,sysdate-begin_date mindate
from t_24
order by mindate)
where r1=1 and rownum=1
BEGIN_DATE最大,sysdate-begin_date就最小
from(
select begin_date,end_date,count(*) over(partition by begin_date,end_date) r1,sysdate-begin_date mindate
from t_24
order by mindate)
where r1=1 and rownum=1