首先数据库里保存的数据如下:
start_date
--------------
2006-07-01
2006-07-02
2006-07-03
2006-07-04
2006-07-10
2006-07-11
2006-07-12
2006-07-13
2006-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 SERVER)答案:
select
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,t.start_date)=1)
(ORACLE)答案:
select
t.start_date,
(select
min(v.start_date)
from
表名 v
where
v.start_date>=t.start_date
and
not exists(select 1 from 表名 where TRUNC (v.start_date, 'dd') - TRUNC (start_date, 'dd') = 1)) from
表名 t
where
not exists(select 1 from 表名 where TRUNC (start_date, 'dd') - TRUNC (t.start_date, 'dd') = 1)