May 2025
Intermediate to advanced
570 pages
7h 38m
Chinese
Content preview from 《SQL 烹饪书》第二版
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
第 10 章 使用范围 使用范围
本作品已使用人工智能进行翻译。欢迎您提供反馈和意见：translation-feedback@oreilly.com
本章介绍涉及范围的 "日常 "查询。范围在日常生活中很常见。例如，我们所做的项目都有连续的时间段。在 SQL 中，经常需要搜索范围、生成范围或以其他方式处理基于范围的数据。这里所介绍的查询比前面几章中的查询稍微复杂一些，但它们同样常见，而且当你学会充分利用 SQL 时，它们会让你对 SQL 的真正功能有所了解。
10.1 定位连续数值范围
问题
您想确定哪些行代表一系列连续的项目。考虑视图 V 中的以下结果集，其中包含有关项目及其开始和结束日期的数据：
select *
from V
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2020 02-JAN-2020
2 02-JAN-2020 03-JAN-2020
3 03-JAN-2020 04-JAN-2020
4 04-JAN-2020 05-JAN-2020
5 06-JAN-2020 07-JAN-2020
6 16-JAN-2020 17-JAN-2020
7 17-JAN-2020 18-JAN-2020
8 18-JAN-2020 19-JAN-2020
9 19-JAN-2020 20-JAN-2020
10 21-JAN-2020 22-JAN-2020
11 26-JAN-2020 27-JAN-2020
12 27-JAN-2020 28-JAN-2020
13 28-JAN-2020 29-JAN-2020
14 29-JAN-2020 30-JAN-2020
除第一行外，每一行的 PROJ_START 都应等于它之前一行的 PROJ_END（"之前 "定义为当前行的 PROJ_ID-1）。从视图 V 中查看前五行，PROJ_ID 1 到 3 属于同一个 "组"，因为每一行的 PROJ_END 都等于其后一行的 PROJ_START。因为要查找连续项目的日期范围，所以要返回当前 PROJ_END 等于下一行 PROJ_START 的所有行。如果整个结果集由前五行组成，则只想返回前三行。最终结果集（使用视图 V 中的所有 14 行）应该是
PROJ_ID PROJ_START PROJ_END
------- ----------- -----------
1 01-JAN-2020 02-JAN-2020
2 02-JAN-2020 03-JAN-2020
3 03-JAN-2020 04-JAN-2020
6 16-JAN-2020 17-JAN-2020
7 17-JAN-2020 18-JAN-2020
8 18-JAN-2020 19-JAN-2020
11 26-JAN-2020 27-JAN-2020
12 27-JAN-2020 28-JAN-2020
13 28-JAN-2020 29-JAN-2020
该结果集中不包括 PROJ_ID为 4、5、9、10 和 14 的记录，因为这些记录的 PROJ_END 与后面记录的 PROJ_START 不匹配。
解决方案
这种解决方案充分利用了窗口函数 LEAD OVER 查看 "下 "一行的 BEGIN_DATE 的功能，从而避免了自连接的需要，而在窗口函数被广泛引入之前，自连接是必须的：
1 select proj_id, proj_start, proj_end 2 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access
More than 5,000 organizations count on O’Reilly
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.Julian F.
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.Addison B.
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.Amir M.
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.