首先数据库里保存的数据如下:
start_date
--------------
2006-07-01
2006-07-02
2006-07-03
2006-07-042006-07-10
2006-07-11
2006-07-12
2006-07-132006-08-01
2006-08-02
2006-08-03
2006-08-04
------------
我如何才能把它们组织成下面这样呢?
start_date
-----------
2006-07-01 2006-07-04
2006-07-10 2006-07-13
2006-08-01 2006-08-04我试了试用sql基本上实现不了。
start_date
--------------
2006-07-01
2006-07-02
2006-07-03
2006-07-042006-07-10
2006-07-11
2006-07-12
2006-07-132006-08-01
2006-08-02
2006-08-03
2006-08-04
------------
我如何才能把它们组织成下面这样呢?
start_date
-----------
2006-07-01 2006-07-04
2006-07-10 2006-07-13
2006-08-01 2006-08-04我试了试用sql基本上实现不了。
t.start_date,
(select
min(v.start_date)
from
表名 v
where
v.start_date>=t.start_date
and
not exists(select 1 from 表名 where datediff(dd,v.start_date,start_date)=1))
from
表名 t
where
not exists(select 1 from 表名 where datediff(dd,start_date,a.start_date)=1)
a.start_date,min(b.start_date)
from
(select
t.*
from
表名 t
where
not exists(select 1 from 表名 where datediff(dd,start_date,t.start_date)=1) a (select
t.*
from
表名 t
where
not exists(select 1 from 表名 where datediff(dd,t.start_date,start_date)=1) b
where
a.start_date<=b.start_date
group by
a.start_date
a.start_date,min(b.start_date)
from
(select
t.*
from
表名 t
where
not exists(select 1 from 表名 where datediff(dd,start_date,t.start_date)=1) a,
(select
t.*
from
表名 t
where
not exists(select 1 from 表名 where datediff(dd,t.start_date,start_date)=1) b
where
a.start_date<=b.start_date
group by
a.start_date
决了!想什么来什么!create table #t
(dates smalldatetime)insert into #t
select '2006-1-1'
union select '2006-1-2'
union select '2006-1-3'
union select '2006-1-4'
union select '2006-1-10'
union select '2006-1-11'
union select '2006-1-12'
union select '2006-1-13'
union select '2006-2-10'
union select '2006-2-11'
union select '2006-3-12'
union select '2006-3-13'select
a.dates,min(b.dates)
from
(
select t.*
from #t t
where not exists(select 1 from #t where datediff(dd,dates,t.dates)=1)
) a inner join
(
select t.*
from #t t
where not exists(select 1 from #t where datediff(dd,t.dates,dates)=1)
) b ON a.dates<=b.dates
group by a.datesdrop table #t
SELECT t.start_date,
(SELECT MIN (v.start_date)
FROM uni_rate v
WHERE v.start_date >= t.start_date
AND NOT EXISTS (
SELECT 1
FROM uni_rate
WHERE TRUNC (v.start_date, 'dd') - TRUNC (start_date, 'dd') =
1))
FROM uni_rate t
WHERE NOT EXISTS (
SELECT 1
FROM uni_rate
WHERE TRUNC (start_date, 'dd') - TRUNC (t.start_date, 'dd') =
1)
请libin_ftsafe(子陌红尘:当libin告别ftsafe) 查看一下有没有改错你的逻辑。
wlzyx() ( )
--------------Oracle没有Datediff函数,直接用2日期相减后乘上系数(小时就是*24)
1楼的sql执行结果如下:
start_date , (.........)
-------------------------
2006-07-04 ,2006-07-10
2006-07-13 ,2006-08-01
2006-08-04能不能把2006-07-01做为开始也显示出来呢?因为开始日期不确定,不一定是1号,也有可能是2、3号。如下:这样就更完美了!
start_date , (.........)
-------------------------
2006-07-01 ,2006-07-04
2006-07-10 ,2006-07-13
2006-08-01 ,2006-08-04