极客教程SQL 定位连续的值区间，你想确定哪些行代表了一系列在时间上连续的项目。考虑下述视图 V 的结果集，它包含了项目编号以及各自的起止日期。
SQL 定位连续的值区间 问题描述
你想确定哪些行代表了一系列在时间上连续的项目。考虑下述视图 V 的结果集，它包含了项目编号以及各自的起止日期。
select *
from V
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
4 04-JAN-2005 05-JAN-2005
5 06-JAN-2005 07-JAN-2005
6 16-JAN-2005 17-JAN-2005
7 17-JAN-2005 18-JAN-2005
8 18-JAN-2005 19-JAN-2005
9 19-JAN-2005 20-JAN-2005
10 21-JAN-2005 22-JAN-2005
11 26-JAN-2005 27-JAN-2005
12 27-JAN-2005 28-JAN-2005
13 28-JAN-2005 29-JAN-2005
14 29-JAN-2005 30-JAN-2005
除了第一行，其他每一行的 PROJ_START 应该等于前一行的 PROJ_END（“前一行”的定义是其 PROJ_ID 等于当前行的 PROJ_ID 减 1）。仔细查看视图 V 的前 5 行，PROJ_ID 分别等于 1 到 3 的行属于同一“组”，因为每一行的 PROJ_END 都等于后一行的 PROJ_START。我们希望找出一系列连续项目的日期区间，因此希望返回满足“当前行的 PROJ_END 等于下一行的 PROJ_START”这一条件的所有行。如果整个结果集只包含前 5 行，那么我们希望返回的只是最前面的 3 行。（对于视图 V 全部 14 行数据而言）最终的结果集应该如下所示。
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
6 16-JAN-2005 17-JAN-2005
7 17-JAN-2005 18-JAN-2005
8 18-JAN-2005 19-JAN-2005
11 26-JAN-2005 27-JAN-2005
12 27-JAN-2005 28-JAN-2005
13 28-JAN-2005 29-JAN-2005
我们从结果集中剔除掉了 PROJ_ID 为 4、5、9、10 和 14 的行，因为这些行的 PROJ_END 不等于下一行的 PROJ_START。
SQL 定位连续的值区间 解决方案
DB2、MySQL、PostgreSQL 和 SQL Server
使用自连接找出包含连续值的行。
1 select v1.proj_id,
2 v1.proj_start,
3 v1.proj_end
4 from V v1, V v2
5 where v1.proj_end = v2.proj_start
Oracle
上述解决方案也适用于 Oracle。除此之外，Oracle 还有另一个解决方案，即利用窗口函数 LEAD OVER 去查看“下一行”的 BEGIN_DATE，这样就不必自连接了。
1 select proj_id,proj_start,proj_end
2 from (
3 select proj_id,proj_start,proj_end,
4 lead(proj_start)over(order by proj_id) next_proj_start
5 from V
6 )
7 where next_proj_start = proj_end
SQL 定位连续的值区间 扩展知识
DB2、MySQL、PostgreSQL 和 SQL Server
通过视图 V 的自连接，每一行都可以与其他行进行比较。考虑查询 PROJ_ID 为 1 和 4 时得到的部分结果集。
select v1.proj_id as v1_id,
v1.proj_end as v1_end,
v2.proj_start as v2_begin,
v2.proj_id as v2_id
from v v1, v v2
where v1.proj_id in ( 1,4 )
V1_ID V1_END V2_BEGIN V2_ID
----- ----------- ----------- ----------
1 02-JAN-2005 01-JAN-2005 1
1 02-JAN-2005 02-JAN-2005 2
1 02-JAN-2005 03-JAN-2005 3
1 02-JAN-2005 04-JAN-2005 4
1 02-JAN-2005 06-JAN-2005 5
1 02-JAN-2005 16-JAN-2005 6
1 02-JAN-2005 17-JAN-2005 7
1 02-JAN-2005 18-JAN-2005 8
1 02-JAN-2005 19-JAN-2005 9
1 02-JAN-2005 21-JAN-2005 10
1 02-JAN-2005 26-JAN-2005 11
1 02-JAN-2005 27-JAN-2005 12
1 02-JAN-2005 28-JAN-2005 13
1 02-JAN-2005 29-JAN-2005 14
4 05-JAN-2005 01-JAN-2005 1
4 05-JAN-2005 02-JAN-2005 2
4 05-JAN-2005 03-JAN-2005 3
4 05-JAN-2005 04-JAN-2005 4
4 05-JAN-2005 06-JAN-2005 5
4 05-JAN-2005 16-JAN-2005 6
4 05-JAN-2005 17-JAN-2005 7
4 05-JAN-2005 18-JAN-2005 8
4 05-JAN-2005 19-JAN-2005 9
4 05-JAN-2005 21-JAN-2005 10
4 05-JAN-2005 26-JAN-2005 11
4 05-JAN-2005 27-JAN-2005 12
4 05-JAN-2005 28-JAN-2005 13
4 05-JAN-2005 29-JAN-2005 14
仔细检查以上结果集，我们就会明白为什么 PROJ_ID 1 被包含在最终结果集中，而 PROJ_ID4 却没有。这是因为对于 V1_ID 4 而言，不存在与 V1_END 值相等的 V2_BEGIN。
如果改变一下筛选条件，PROJ_ID 4 也可以被认为是相邻的项目。考虑如下所示的结果集。
select *
from V
where proj_id <= 5
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
4 04-JAN-2005 05-JAN-2005
5 06-JAN-2005 07-JAN-2005
如果“相邻的项目”是指其开始日期与另一个项目的结束日期相同，那么 PROJ_ID 4 也应该被包含在结果集里。按照最初的筛选条件，由于前向比较（比较 PROJ_END 和下一行的 PROJ_START）的存在，PROJ_ID 4 被排除在外了，但如果做后向比较（比较 PROJ_START 和前一行的 PROJ_END），那么 PROJ_ID 4 就应该被包含在结果集中。
不妨修改一下上述解决方案，把 PROJ_ID 4 也包含进去：只需增加一个筛选条件，确保 PROJ_START 和 PROJ_END 的相邻关系都被纳入检查范围就行了，而不是仅仅检查 PROJ_END。下面的查询展示了上述改动，该查询产生了一个包含 PROJ_ID 4 的结果集（DISTINCT 是必要的，因为一些行同时满足两个条件）。
select distinct
v1.proj_id,
v1.proj_start,
v1.proj_end
from V v1, V v2
where v1.proj_end = v2.proj_start
or v1.proj_start = v2.proj_end
PROJ_ID PROJ_START PROJ_END
------ ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
4 04-JAN-2005 05-JAN-2005
Oracle
上述自连接方案当然适用于本问题，不过窗口函数 LEAD OVER 用于解决这一类问题更合适。LEAD OVER 函数可以不执行自连接就能查看其他行的数据（尽管该函数要求必须对结果集进行排序）。对于 PROJ_ID1 和 PROJ_ID4，考虑内嵌视图（第 3 ～ 5 行）的结果集。
select *
from (
select proj_id,proj_start,proj_end,
lead(proj_start)over(order by proj_id) next_proj_start
from v
)
where proj_id in ( 1,4 )
PROJ_ID PROJ_START PROJ_END NEXT_PROJ_START
------- ----------- ----------- ---------------
1 01-JAN-2005 02-JAN-2005 02-JAN-2005
4 04-JAN-2005 05-JAN-2005 06-JAN-2005
仔细检查上面的代码片段及其结果集，不难理解为什么 PROJ_ID 4 会被从最终结果集里排除掉。这是因为 PROJ_END 的日期 2005 年 1 月 5 日与下一个项目的开始日期 2005 年 1 月 6 日不同。
LEAD OVER 函数用于解决这一类问题非常方便，尤其在只遍历部分结果的时候。使用窗口函数时，要记得它们在 FROM 和 WHERE 子句之后才会被评估，因此前面的查询里的 LEAD OVER 函数必须被放入一个内嵌视图才行。否则，LEAD OVER 函数的作用对象就变成了只含有 PROJ_ID 1 和 PROJ_ID 4 的结果集，因为执行过 WHERE 子句之后，其他行都会被过滤掉。
现在，如果改变一下筛选条件，我们也能把 PROJ_ID 4 纳入最终的结果集。考虑视图 V 的前 5 行数据。
select *
from V
where proj_id <= 5
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
4 04-JAN-2005 05-JAN-2005
5 06-JAN-2005 07-JAN-2005
如果我们认为 PROJ_ID 4 事实上是相邻的（因为 PROJ_ID 4 的 PROJ_START 等于 PROJ_ID 3 的 PROJ_END），而且只有 PROJ_ID 5 应该被排除在外，那么前述解决方案就不正确了，至少是不全面的。
select proj_id,proj_start,proj_end
from (
select proj_id,proj_start,proj_end,
lead(proj_start)over(order by proj_id) next_start
from V
where proj_id <= 5
)
where proj_end = next_start
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
如果我们认为 PROJ_ID 4 应该被包含进来，只需额外加上 LAG OVER 函数，并在 WHERE 子句中也增加一个过滤条件即可。
select proj_id,proj_start,proj_end
from (
select proj_id,proj_start,proj_end,
lead(proj_start)over(order by proj_id) next_start,
lag(proj_end)over(order by proj_id) last_end
from V
where proj_id <= 5
)
where proj_end = next_start
or proj_start = last_end
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2005 02-JAN-2005
2 02-JAN-2005 03-JAN-2005
3 03-JAN-2005 04-JAN-2005
4 04-JAN-2005 05-JAN-2005
这样一来，PROJ_ID 4 也被纳入最终结果集了，只有 PROJ_ID 5 会被排除在外。当然，如果真的要对本解决方案做出上述代码改动，一定仔细确认需求。