1-检索记录
1.1 检索所有行和列
在交互式即席查询中，使用select * 会更容易，然而，在编写程序代码时，最好具体指明每一列。
1.8 在 SELECT 语句里使用条件逻辑
SELECT ename,sal,CASE WHEN sal <= 2000 THEN 'UNDERPAID'WHEN sal >= 4000 THEN 'OVERPAID'ELSE 'OK'END AS STATUSFROM emp
1.10 随机返回若干行记录
MySQL
把内置函数RAND和limit 、ORDER BY结合使用
SELECT ename,jobFROM empORDER BY RAND() LIMIT 5
1.11 查找Null值
Null值不会等于或者不等于任何值，甚至不能与其自身比较。因此，不能用= 或者 != 来测试某一列的值是否为Null。判断一行是否含有Null，必须使用IS Null。也可以使用 IS NOT Null 来找到给定列的值不是Null的所有行。
1.12 把Null值转换成实际值
select coalesce(comm,0) from emp
COALESCE函数恰好适用于所有数据库。
2 查询结果排序
2.5 排序时对Null值的处理
问题：想按照EMP表的COMM列队查询结果进行排序，但是该字段可能为Null。因此，你需要想个办法来指定是否应该将Null值排到后面。
SELECT ename,sal,commFROM (SELECT ename,sal,comm,CASE WHEN comm IS NULL THEN 0 ELSE 1 END AS is_nullFROM emp)XORDER BY is_null ,comm
2.6 依据条件逻辑动态调整排序项
希望按照某个条件逻辑来排序。例如，如果 JOB 等于 SALESMAN ，就要按照 COMM 来排序； 否则，按照 SAL 排序。你希望返回如下所示的结果集.
在ORDER BY子句中使用CASE
SELECT ename,sal,job,commFROM empORDER BY CASE WHEN job = 'SALESMAN' THEN comm ELSE sal END
利用CASE动态来调整结果的排序
SELECT ename,sal,job,comm,CASE WHEN job = 'SALESMAN' THEN comm ELSE sal END AS orderedFROM empORDER BY 5
3 多表查询
3.4 查找只存在于一个表中的数据
找出在DEPT表中存在而EMP表却不存在的部门编号。在示例数据库中，DEPT表为40的数据并不在EMP表中。
解决方案：计算差集
MySQL
SELECT deptnoFROM deptWHERE deptno NOT IN (SELECT deptno FROM emp)
Oracle
使用集合运算MINUS
select deptno from deptminusselect deptno from emp
MySQL和SQL Server
这个子查询会获取EMP表中所有的 DEPTNO，外层查询会返回DEPT表中“不存在于”或“未被包含”在子查询结果集里的所有的DEPTNO值。
在MySQL和SQL Server解决方案时，需要考虑排查重复项。其他数据库基于EXCEPT或MINUS的解决方案已经从结果集中排除了重复的行，确保每个DEPTNO只出现一次。当然，之所以能这样做，是因为示例数据中的DEPTNO是表的主键，如果不是主键，可以使用DISTINCT来确保每个在EMP表里缺少的DEPTNO值只出现一次，如下
select distinct deptnofrom deptwhere deptno not in (select deptno from emp)
在使用NOT IN时，要注意Null值，考虑如下表NEW_DEPT.
create table new_dept(deptno integer);insert into new_dept values (10);insert into new_dept values (50);insert into new_dept values (null);
使用 NOT IN 子查询检索存在于 DEPT 表却不存在于 NEW_DEPT 表的 DEPTNO，会发现查不到任何值
SELECT *FROM deptWHERE deptno NOT IN (SELECT deptno FROM new_dept)
DEPTNO为20,30,40 的数据虽然不再NEW_DEPT中，却没有被查询到。原因在于NEW_DEPT表里有Null值。子查询会返回3行DEPTNO，分别为10,50，和NULL值。**in** 和 **not in** 本质上是OR运算，由于NULL值参与OR逻辑运算的方式不同， **IN** 和 **NOT IN** 将会产生不同的结果。
select deptnofrom deptwhere deptno in ( 10,50,null )DEPTNO-------10select deptnofrom deptwhere (deptno=10 or deptno=50 or deptno=null)DEPTNO-------10
使用 NOT IN和 NOT OR的例子
select deptnofrom deptwhere deptno not in ( 10,50,null )( no rows )select deptnofrom deptwhere not (deptno=10 or deptno=50 or deptno=null)( no rows )
条件DEPTNO NOT IN (10, 50, NULL) 等价于 not (deptno=10 or deptno=50 or deptno=null)。
为避免NOT IN 和Null值带来的问题，需要结合NOT EXISTS和关联查询。关联查询指的是外层查询执行后获得的结果集会被爱内层子查询引用。下面是一个免受Null值影响的替代方案。
SELECT d.deptnoFROM dept dWHERE NOT EXISTS ( SELECT NULLFROM emp eWHERE d.deptno = e.deptno )
3.5 从一个表检索与另一个表不相关的行
select d.*from dept d left outer join emp eon (d.deptno = e.deptno)where e.deptno is null
3.7 确定两个表是否有相同的数据
CREATE VIEW VASSELECT * FROM emp WHERE deptno != 10UNION ALLSELECT * FROM emp WHERE ename = 'WARD'
3.9 组合使用连接查询与聚合函数
想执行一个聚合操作，但查询的语句设计多个表。你希望确保表之间的连接查询不会干扰聚合操作。例如，计算部分编号为10的员工的工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在EMP表和EMP_BONUS表连接之后在执行聚合函数SUM，就会得出出的的计算结果。
SELECT * FROM emp_bonus ;----------------------------EMPNO RECEIVED TYPE------ ---------- --------7934 2005-03-17 17934 2005-02-15 27839 2005-02-15 37782 2005-02-15 1
下面的查询语句，它返回了部门编号为10的所有员工的工资和奖金。BONUS表中TYPE列决定了奖金的数额，若TYPE等于1，则奖金为工资的10%。若TYPE为2，则奖金为工资的20%。若TYPE为3，则奖金为工资的30%。
SELECT e.empno,e.ename,e.sal,e.deptno,e.sal*CASE WHEN eb.type = 1 THEN .1WHEN eb.type = 2 THEN .2ELSE .3END AS bonusFROM emp e, emp_bonus ebWHERE e.empno = eb.empnoAND e.deptno = 10--------------------------------------empno ename sal deptno bonus------ ------ ------ ------ --------7934 MILLER 1300 10 260.07934 MILLER 1300 10 130.07839 KING 5000 10 1500.07782 CLARK 2450 10 245.0
连接EMP_BOUNS表并计算奖金总和。就会出错。
sELECT deptno,SUM(sal) AS total_sal,SUM(bonus) AS total_bonusFROM (SELECT e.empno,e.ename,e.sal,e.deptno,e.sal*CASE WHEN eb.type = 1 THEN .1WHEN eb.type = 2 THEN .2ELSE .3END AS bonusFROM emp e, emp_bonus ebWHERE e.empno = eb.empnoAND e.deptno = 10)XGROUP BY deptno-------------------------------deptno total_sal total_bonus------ --------- -------------10 10050 2135.0
尽管奖金是正确的，但是工资总和不正确，因为有人出现2次。
解决方案
在连接查询里进行聚合远算时，必须十分小小。如果连接查询产生了重复行，通常有两种办法来使用聚合函数，而且可以避免得出错误的计算结果。一种方法是，调用聚合函数时直接使用关键字DISTINCT，这样每个值都会先去掉重复项在参与计算；另一种方法是，在进行连接查询之前先执行聚合运算(以内嵌视图的方式)，这样可以避免错误的结果，因为聚合运算发生在连接查询之前。
-- 使用distinct解决SELECT deptno,SUM(DISTINCT sal) AS total_sal,SUM(bonus) AS total_bonusFROM (SELECT e.empno,e.ename,e.sal,e.deptno,e.sal*CASE WHEN eb.type = 1 THEN .1WHEN eb.type = 2 THEN .2ELSE .3END AS bonusFROM emp e, emp_bonus ebWHERE e.empno = eb.empnoAND e.deptno = 10)XGROUP BY deptno
4 插入、更新和删除
6 字符串处理
6.1 遍历字符串
8 日期运算
8.1 年月日加减法
Oracle
select hiredate - 5 as hd_minus_5D,hiredate + 5 as hd_plus_5D,add_months(hiredate, -5) as hd_minus_5M,add_months(hiredate, 5) as hd_plus_5M,add_months(hiredate, -5 * 12) as hd_minus_5Y,add_months(hiredate, 5 * 12) as hd_plus_5Yfrom empwhere deptno = 10 ;
MySQL
使用加减法，并使用INTERVAL关键字指定要加上或者减去的时间单位。
SELECThiredate - INTERVAL 5 DAY AS hd_minus_5D,hiredate + INTERVAL 5 DAY AS hd_plus_5D,hiredate - INTERVAL 5 MONTH AS hd_minus_5M,hiredate + INTERVAL 5 MONTH AS hd_plus_5M,hiredate - INTERVAL 5 YEAR AS hd_minus_5Y,hiredate + INTERVAL 5 YEAR AS hd_plus_5YFROMempWHERE deptno = 10 ;-- 或者SELECTDATE_ADD(hiredate, INTERVAL - 5 DAY) AS hd_minus_5D,DATE_ADD(hiredate, INTERVAL 5 DAY) AS hd_plus_5D,DATE_ADD(hiredate, INTERVAL - 5 MONTH) AS hd_minus_5M,DATE_ADD(hiredate, INTERVAL 5 MONTH) AS hd_plus_5M,DATE_ADD(hiredate, INTERVAL - 5 YEAR) AS hd_minus_5Y,DATE_ADD(hiredate, INTERVAL 5 YEAR) AS hd_plus_5DYFROMempWHERE deptno = 10 ;
8.2 计算两个日期之间的天数
MySQL
使用DATEDIFF函数找出两个日期之间相差多少天。需要较早的日期之应该作为第一个参数以免出现负数。(mariadb不同 https://mariadb.com/kb/en/datediff/)
SELECTDATEDIFF(allen_hd, ward_hd)FROM(SELECThiredate AS ward_hdFROMempWHERE ename = 'WARD') X,(SELECThiredate AS allen_hdFROMempWHERE ename = 'ALLEN') Y
10 区间查询
10.1 定位连续的值区间
MySQL建表语句
CREATE TABLE V (PROJ_ID INTEGER NOT NULL,PROJ_START DATE,PROJ_END DATE) ENGINE = INNODB DEFAULT CHARSET = utf8 ;INSERT INTO V VALUES ('1', '2005-01-01','2005-01-02');INSERT INTO V VALUES ('2', '2005-01-02','2005-01-03');INSERT INTO V VALUES ('3', '2005-01-03','2005-01-04');INSERT INTO V VALUES ('4', '2005-01-04','2005-01-05');INSERT INTO V VALUES ('5', '2005-01-06','2005-01-07');INSERT INTO V VALUES ('6', '2005-01-16','2005-01-17');INSERT INTO V VALUES ('7', '2005-01-17','2005-01-18');INSERT INTO V VALUES ('8', '2005-01-18','2005-01-19');INSERT INTO V VALUES ('9', '2005-01-19','2005-01-20');INSERT INTO V VALUES ('10', '2005-01-21','2005-01-22');INSERT INTO V VALUES ('11', '2005-01-26','2005-01-27');INSERT INTO V VALUES ('12', '2005-01-27','2005-01-28');INSERT INTO V VALUES ('13', '2005-01-28','2005-01-29');INSERT INTO V VALUES ('14', '2005-01-29','2005-01-30');
除了第一行，其他每一行的PROJ_START应该等于前一行的PROJ_END（“前一行”的定义是其 PROJ_ID 等于当前行的 PROJ_ID 减 1）。
希望找到满足“当前行的PROJ_END等于下一行的PROJ_START”这一条件的所有行。如果只统计前5行，希望返回的是最前面3行。
MySQL
下面这语句也适用于Oracle
SELECTv1.proj_id,v1.proj_start,v1.proj_endFROMV v1,V v2WHERE v1.proj_end = v2.proj_start
oracle
Oracle还有一个窗口函数 LEAD OVER去查看下一行的BEGIN_DATE,这样就不必自连接了。
select proj_id, proj_start, proj_endfrom (select proj_id,proj_start,proj_end,lead(proj_start) over(order by proj_id) next_proj_startfrom V)where next_proj_start = proj_end ;
10.2 计算同一组或分区的行之间的差
想返回每个员工的DEPTNO、ENAME、SAL以及同一个部门(即DEPTNO相同)里的不同员工之间的工资差距。工资差距指的是当前员工的SAL和入职紧随其后的那个员工的SAL之间的差值。
解决方案
Oracle的窗口函数LEAD OVER和LAG OVER十分便利，不需要做额外的连接查询，就能方便的看下一行或者前一行。对于其他关系数据库，可以使用标量子查询。
oracle
利用窗口函数LEAD OVER读取与当前行相关的下一个员工工资
select deptno,
ename,
sal,
next_sal,
hiredate,
lpad(nvl(to_char(sal - next_sal), 'N/A'), 10) diff
from (select deptno,
ename,
sal,
hiredate,
lead(sal) over(partition by deptno order by hiredate) next_sal
from emp)
MySQL
SELECT
deptno,
ename,
hiredate,
sal,
COALESCE(CAST(sal - next_sal AS CHAR(10)), 'N/A') AS diff
FROM
(SELECT
e.deptno,
e.ename,
e.hiredate,
e.sal,
(SELECT
MIN(sal)
FROM
emp d
WHERE d.deptno = e.deptno
AND d.hiredate =
(SELECT
MIN(hiredate)
FROM
emp d
WHERE e.deptno = d.deptno
AND d.hiredate > e.hiredate)) AS next_sal
FROM
emp e) X
解析
首先显示标量子查询找出同一个部门里紧随当前员工之后入职的HIRDARE，上述中标量子查询中使用了 MIN(HIREDATE) 来确保仅返回一个值，即使同一天入职的员工不止一个人，也只会返回一个值。
SELECT e.deptno,
e.ename,
e.hiredate,
e.sal,
(SELECT MIN(hiredate)
FROM emp d
WHERE e.deptno = d.deptno
AND d.hiredate > e.hiredate) AS next_hire
FROM emp e
ORDER BY 1;
然后使用另一个标量子查询找出日期等于NEXT_HIRE的员工的工资
select e.deptno,
e.ename,
e.hiredate,
e.sal,
(select min(sal)
from emp d
where d.deptno = e.deptno
and d.hiredate = (select min(hiredate)
from emp d
where e.deptno = d.deptno
and d.hiredate > e.hiredate)) as next_sal
from emp e
order by 1
最后，计算SAL和 NEXT_SAL之间的差。并且使用COALESCE函数在适当的时候处理NULL。
Oracle解析
利用窗口函数LEAD OVER为每个员工找出部门中的下一个工资值。对于每个部分里最迟入职的员工，其NEXT_SAL列会是Null。
select deptno,
ename,
sal,
hiredate,
lead(sal) over(partition by deptno order by hiredate) next_sal
from emp order by 4
----------------------------
DEPTNO ENAME SAL HIREDATE NEXT_SAL
---------- ---------- ---------- ----------- ----------
20 SMITH 800 1980-12-17 2975
30 ALLEN 1600 1981-02-20 1250
30 WARD 1250 1981-02-22 2850
20 JONES 2975 1981-04-02 3000
30 BLAKE 2850 1981-05-01 1500
10 CLARK 2450 1981-06-09 5000
30 TURNER 1500 1981-09-08 1250
30 MARTIN 1250 1981-09-28 950
10 KING 5000 1981-11-17 1300
30 JAMES 950 1981-12-03
20 FORD 3000 1981-12-03 3000
10 MILLER 1300 1982-01-23
20 SCOTT 3000 1982-12-09 1100
20 ADAMS 1100 1983-01-12
然后计算同一个部门里每个员工与紧随其后入职的员工的工资差。
select deptno, ename, sal, hiredate, sal - next_sal diff
from (select deptno,
ename,
sal,
hiredate,
lead(sal) over(partition by deptno order by hiredate) next_sal
from emp)
上述例子中没有讨论特例情况，例如使用Oracle的LEAD OVER函数时需要注意重复项的问题。对于EMP那些表中，并不存在HIREDATE相同的行。考虑如下查询，该查询返回部门编号为10的员工之间的工资差距。
select deptno,
ename,
sal,
hiredate,
lpad(nvl(to_char(sal - next_sal), 'N/A'), 10) diff
from (select deptno,
ename,
sal,
hiredate,
lead(sal) over(partition by deptno order by hiredate) next_sal
from emp
where deptno = 10
and empno > 10)
------------------------
DEPTNO ENAME SAL HIREDATE DIFF
---------- ---------- ---------- ----------- ----------------------------------------
10 CLARK 2450 1981-06-09 -2550
10 KING 5000 1981-11-17 3700
10 MILLER 1300 1982-01-23 N/A
如果另外有4名员工和员工KING同一天入职。
insert into emp (empno,ename,deptno,sal,hiredate)
values (1,'ant',10,1000,to_date('1981-11-17'));
insert into emp (empno,ename,deptno,sal,hiredate)
values (2,'joe',10,1500,to_date('1981-11-17'));
insert into emp (empno,ename,deptno,sal,hiredate)
values (3,'jim',10,1600,to_date('1981-11-17'));
insert into emp (empno,ename,deptno,sal,hiredate)
values (4,'jon',10,1700,to_date('1981-11-17'));
select deptno,
ename,
sal,
next_sal,
hiredate,
lpad(nvl(to_char(sal - next_sal), 'N/A'), 10) diff
from (select deptno,
ename,
sal,
hiredate,
lead(sal) over(partition by deptno order by hiredate) next_sal
from emp
where deptno = 10)
查询结果发现，有些入职同一天的员工在相互比较。这不是期望的。Oracle运行我们通过传递一个额外的参数告诉LEAD OVER函数应该往前看几行。就本例而言，只需要一个计数：找出每一个在11月17日入职的员工和1月23日之间的距离。
select deptno,
ename,
sal,
hiredate,
lpad(nvl(to_char(sal - next_sal), 'N/A'), 10) diff
from (select deptno,
ename,
sal,
hiredate,
lead(sal, cnt - rn + 1) over(partition by deptno order by hiredate) next_sal
from (select deptno,
ename,
sal,
hiredate,
count(*) over(partition by deptno, hiredate) cnt,
row_number() over(partition by deptno, hiredate order by sal) rn
from emp
where deptno = 10)
);
CNT-RN+1 代表每一个在11月17日入职的员工到MILLER的距离。
10.3 定位连续值区间的开始值和结束值
CREATE TABLE `v` (
`PROJ_ID` int(11) NOT NULL,
`PROJ_START` date DEFAULT NULL,
`PROJ_END` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `v` */
insert into `v`(`PROJ_ID`,`PROJ_START`,`PROJ_END`) values (1,'2005-01-01','2005-01-02'),(2,'2005-01-02','2005-01-03'),(3,'2005-01-03','2005-01-04'),(4,'2005-01-04','2005-01-05'),(5,'2005-01-06','2005-01-07'),(6,'2005-01-16','2005-01-17'),(7,'2005-01-17','2005-01-18'),(8,'2005-01-18','2005-01-19'),(9,'2005-01-19','2005-01-20'),(10,'2005-01-21','2005-01-22'),(11,'2005-01-26','2005-01-27'),(12,'2005-01-27','2005-01-28'),(13,'2005-01-28','2005-01-29'),(14,'2005-01-29','2005-01-30');
oracle建表语句
create table vv
(
proj_id INTEGER not null,
proj_start DATE,
proj_end DATE
);
insert into vv (PROJ_ID, PROJ_START, PROJ_END)
vvalues ('1', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('02-01-2005', 'dd-mm-yyyy'));
insert into vv (PROJ_ID, PROJ_START, PROJ_END)
vvalues ('2', to_date('02-01-2005', 'dd-mm-yyyy'), to_date('03-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('3', to_date('03-01-2005', 'dd-mm-yyyy'), to_date('04-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('4', to_date('04-01-2005', 'dd-mm-yyyy'), to_date('05-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('5', to_date('06-01-2005', 'dd-mm-yyyy'), to_date('07-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('6', to_date('16-01-2005', 'dd-mm-yyyy'), to_date('17-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('7', to_date('17-01-2005', 'dd-mm-yyyy'), to_date('18-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('8', to_date('18-01-2005', 'dd-mm-yyyy'), to_date('19-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('9', to_date('19-01-2005', 'dd-mm-yyyy'), to_date('20-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('10', to_date('21-01-2005', 'dd-mm-yyyy'), to_date('22-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('11', to_date('26-01-2005', 'dd-mm-yyyy'), to_date('27-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('12', to_date('27-01-2005', 'dd-mm-yyyy'), to_date('28-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('13', to_date('28-01-2005', 'dd-mm-yyyy'), to_date('29-01-2005', 'dd-mm-yyyy'));
insert into v (PROJ_ID, PROJ_START, PROJ_END)
values ('14', to_date('29-01-2005', 'dd-mm-yyyy'), to_date('30-01-2005', 'dd-mm-yyyy'));
如果有一行不属于某个连续值的区间，你仍然希望返回它(这样的行自成一个区间)
解决方案
区间：PROJ_STARThe PROJ_END的值决定哪些行属于同一区间如果某一行的 PROJ_START 值等于上一行的PROJ_END 值，那么该行就是“连续”的，或者说它属于某个组。如果某一行的 PROJ_START值不等于上一行的 PROJ_END 值，并且它的 PROJ_END 值也不等于下一行的 PROJ_START 值，那么该行自身就构成了一个独立的组。识别出区间之后，还要对每个区间相关的行进行分组，并找出每一组的开始值和结束值。
MySQL
构建一个视图
create view v2
as
select a.*,
case
when (select b.proj_id from V b where a.proj_start = b.proj_end) is not null then
0
else
1
end as flag
from V a ;
select * from V2 ;
其结果集如下
SQL> select * from V2 ;
PROJ_ID PROJ_START PROJ_END FLAG
------- ----------- ----------- ----------
1 2005-01-01 2005-01-02 1
2 2005-01-02 2005-01-03 0
3 2005-01-03 2005-01-04 0
4 2005-01-04 2005-01-05 0
5 2005-01-06 2005-01-07 1
6 2005-01-16 2005-01-17 1
7 2005-01-17 2005-01-18 0
8 2005-01-18 2005-01-19 0
9 2005-01-19 2005-01-20 0
10 2005-01-21 2005-01-22 1
11 2005-01-26 2005-01-27 1
12 2005-01-27 2005-01-28 0
13 2005-01-28 2005-01-29 0
14 2005-01-29 2005-01-30 0
然后在视图的基础上得到解决方案。首先，找出那些属于连续值区间的行，并为它们分组。然后调用MIN函数和MAX函数找出每一组的开始值和结束值。
select proj_grp, min(proj_start) as proj_start, max(proj_end) as proj_end
from (select a.proj_id,
a.proj_start,
a.proj_end,
(select sum(b.flag) from V2 b where b.proj_id <= a.proj_id) as proj_grp
from V2 a) x
group by proj_grp;
Oracle
上述也适用于Oracle，不过Oracle的窗口函数LAG OVER，无需额外的视图也能解决。
select proj_grp, min(proj_start), max(proj_end)
from (select proj_id,
proj_start,
proj_end,
sum(flag) over(order by proj_id) proj_grp
from (select proj_id,
proj_start,
proj_end,
case
when lag(proj_end)
over(order by proj_id) = proj_start then
0
else
1
end flag
from V))
group by proj_grp;
对于本例而言，窗口函数LAG OVER非常有用，我们无需使用自连接、标量查询或额外的视图就能访问前一行的PROJ_END的值。
select proj_id,
proj_start,
proj_end,
lag(proj_end) over(order by proj_id) prior_proj_end
from V ;
---------------------------------------------
PROJ_ID PROJ_START PROJ_END PRIOR_PROJ_END
------- ----------- ----------- --------------
1 2005-01-01 2005-01-02
2 2005-01-02 2005-01-03 2005-01-02
3 2005-01-03 2005-01-04 2005-01-03
4 2005-01-04 2005-01-05 2005-01-04
5 2005-01-06 2005-01-07 2005-01-05
6 2005-01-16 2005-01-17 2005-01-07
7 2005-01-17 2005-01-18 2005-01-17
8 2005-01-18 2005-01-19 2005-01-18
9 2005-01-19 2005-01-20 2005-01-19
10 2005-01-21 2005-01-22 2005-01-20
11 2005-01-26 2005-01-27 2005-01-22
12 2005-01-27 2005-01-28 2005-01-27
13 2005-01-28 2005-01-29 2005-01-28
14 2005-01-29 2005-01-30 2005-01-29
10.4 为值区间填充缺失值
问题：列出整个20世界80年代里每年入职的员工人数。但有一些年份没有新员工入职。
解决方案：关键在于如何为那些没有新增加员工的年份返回0。如果在一个给定的年份里没有新入职的员工，那么EMP表里就不存在对应的行。既然EMP表里不存在对应的行，该如何为这一年份返回计数值0呢？本次解决方案需要用到外连接操作，我们拼凑一个包含了所有目标年份的结果集，然后针对EMP表执行COUNT查询。以判断每一年里是否增加了新员工。
-- oracle
select x.yr, coalesce(cnt, 0) cnt
from (select extract(year from min(hiredate) over()) -
mod(extract(year from min(hiredate) over()), 10) + rownum - 1 yr
from emp
where rownum <= 10) x
left join (select to_number(to_char(hiredate, 'YYYY')) yr, count(*) cnt
from emp
group by to_number(to_char(hiredate, 'YYYY'))) y
on (x.yr = y.yr);
解释：各个数据库的解决方案语法有所不同，但是做法却类似。内嵌视图X找出最早的HIREDATE值对应的年份，进而返回20世界80年代的每一年。下一步是用最早的年份减去该年份摸10计算的结果，然后在加上RN-1。下面列出使用内嵌视图X，分别使用窗口函数MIN OVER(DB2 Oracle SQL Server)和 标量子查询(mysql PostgreSQL)来得到对应的结果集。 未完待续
10.5 生成连续的值
Oracle
使用CONNECT BY递归查询(适用于Oracle9i以及后续版本)。如果使用Oracle9i，需要把CONNECT BY放入一个内嵌视图，要么把它放进WITH子句。
oracle10g以及后续版本，可以使用MODEL子句
select array id
from dual model dimension by(0 idx) measures(1 array) rules iterate(10)(array [ iteration_number ] = iteration_number + 1);
11 高级查询
11.1 结果集分页
问题：希望从EMP返回最前的5条工资记录
解决方案：SQL里并没有“最先”，“最后”，“下一条”的概念。我们必须对记录行做出明确的排序。只有做过了排序，才有可能准确地从结果集中返回指定区间的记录。
DB2 Oracle SQL Server
使用窗口函数ROW_NUMBER OVER实现排序，并且在WHERE子句中指定我们希望返回的行。
select sal
from (select row_number() over(order by sal) as rn, sal from emp) x
where rn between 1 and 5
改变WHERE子句，我们能返回任意区间的行。
11.2 跳过n行记录
问题：查询来隔行返回EMP表中的记录。希望获得第一员工，第三员工等。例如从下面的结果集
ENAME
--------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
希望返回的是
ENAME
----------
ADAMS
BLAKE
FORD
JONES
MARTIN
SCOTT
TURNER
解决方案
为了从一个结果集中跳过第二行，第4行，我们必须对结果集排序，否则就没有所谓的，第一个，下一个等概念。
DB3 Oracle SQL Server
使用窗口函数ROW_NUMBER OVER为每一行分配一个序号。
select ename
from (
select row_number() over (order by ename) rn,
ename
from emp
) x
where mod(rn,2) = 1
MySQL和PostgreSQL
这两种数据库不提供排序或每一行数据编排序号的内置函数，因而需要使用标量来查询模拟实现这类功能(本例总根据员工名字排序)
select x.ename
from (
select a.ename,
(select count(*)
from emp b
where b.ename <= a.ename) as rn
from emp a
)x
where mod(x.rn,2) = 1
---------------------------
select a.ename,
(select count(*) from emp b where b.ename <= a.ename)
as rn
from emp a order by 2
-----------
ENAME RN
---------- ----------
ADAMS 1
ALLEN 2
BLAKE 3
CLARK 4
FORD 5
JAMES 6
JONES 7
KING 8
MARTIN 9
MILLER 10
SCOTT 11
SMITH 12
TURNER 13
WARD 14
ant 15
jim 16
joe 17
jon 18
mairadb10.2开始提供了ROW_NUMBER()函数
11.3 在外连接查询里使用OR逻辑
问题：想查询部门编号为10和20的全体员工的名字和部门信息，以及部门编号为30和40的部门信息(不包含员工信息)。
最初试图这样做
select e.ename, d.deptno, d.dname, d.loc
from dept d, emp e
where d.deptno = e.deptno
and (e.deptno = 10 or e.deptno = 20)
order by 2
-------------
ENAME DEPTNO DNAME LOC
---------- ---------- -------------- -------------
MILLER 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
FORD 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
以上连接时内连接，因此返回的结果集不包含DEPTNO是20和30的部门信息。
在下面的查询中视图将emp表外连接到DEPT，但仍然没有正确的结果集
select e.ename, d.deptno, d.dname, d.loc
from dept d
left join emp e
on (d.deptno = e.deptno)
where e.deptno = 10
or e.deptno = 20
order by 2
---
ENAME DEPTNO DNAME LOC
---------- ---------- -------------- -------------
MILLER 10 ACCOUNTING NEW YORK
CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
FORD 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
解决方案
DB2 MySQL PostgreSQL SQL Server
把or条件移到JOIN子句中。
select e.ename, d.deptno, d.dname, d.loc
from dept d
left join emp e
on (d.deptno = e.deptno and (e.deptno = 10 or e.deptno = 20))
order by 2
--- 内嵌视图
select e.ename, d.deptno, d.dname, d.loc
from dept d
left join (select ename, deptno from emp where deptno in (10, 20)) e
on (e.deptno = d.deptno)
order by 2
第一种把OR条件移到JOIN子句，使它成为连接条件的一部分，这样一来，我们既能筛选出EMP表的数据，又不会丢掉DEPT表里DEPTNO 等于 30 和 40 的数据。
第二种方案把过滤条件移到了内嵌试图，内嵌视图 E 基于 EMP.DEPTNO 过滤数据。从EMP 表里提取出我们感兴趣的行。然后，这些行被外连接到 DEPT 表。 DEPT 表是外连接的基础表，因此包括部门编号为 30 和 40 在内的所有部门都会被返回。
11.4 识别互逆的记录
create table vce
(
TEST1 INTEGER not null,
TEST2 INTEGER not null
);
-- oracle 的多行插入
insert all
into vce VALUES(20 ,20 )
into vce VALUES(50 ,25 )
into vce VALUES(20 ,20 )
into vce VALUES(60 ,30 )
into vce VALUES(70 ,90 )
into vce VALUES(80 ,130 )
into vce VALUES(90 ,70 )
into vce VALUES(100, 50 )
into vce VALUES(110, 55 )
into vce VALUES(120, 60 )
into vce VALUES(130, 80 )
into vce VALUES(140, 70 )
SELECT 1 FROM DUAL;
select * from vce;
TEST1 TEST2
-------------
20 20
50 25
20 20
60 30
70 90
80 130
90 70
100 50
110 55
120 60
130 80
140 70
数据中TEST等于70考试分数和TEST2等于90的考试分数是互逆的（ TEST1 存在一个 90 的分数，并且 TEST2 也存在一个 70 的分数）。
希望得到的是如下结果
TEST1 TEST2
----- ----------
20 20
70 90
80 130
而不是下面这个
TEST1 TEST2
----- ----------
20 20
20 20
70 90
80 130
90 70
130 80
解决方案
select distinct v1.*
from Vce v1, Vce v2
where v1.test1 = v2.test2
and v1.test2 = v2.test1
and v1.test1 <= v1.test2;
11.5 提取最靠前的n行记录
DB2 Oracle SQL Server
使用窗口函数DENSE_RANK 函数。这意味着每一个Tie只会被统计一次。
Tie：在排序计算过程中，如果一个名次出现了多个候选项，则每一个候选项可称为一个Tie。
SELECT ename, sal
FROM (SELECT ename, sal, DENSE_RANK() OVER(ORDER BY sal DESC) dr FROM emp) X
WHERE dr <= 5
上述查询返回的行数可能超过5行。但只有5中不同的工资值。如果不考虑Tie，只返回5行记录的话，那就使用ROW_NUMBER OVER(因为该函数不关心Tie)
MySQL和PostgreSQL
使用标量子查询为每个工资值创建一个序号，然后通过上述序号限制子查询的结果行数
SELECT *
FROM (SELECT (SELECT COUNT(DISTINCT b. sal)
FROM emp b
WHERE a. sal <= b. sal) AS rnk,
a.sal,
a.ename
FROM emp a)
WHERE rnk <= 5;
11.6 找出最大 和最小的记录
找出EMP表中工资最高和最低的员工。
DB2 Oracle SQL Server
select ename
from (select ename,
sal,
min(sal) over() min_sal,
max(sal) over() max_sal
from emp) x
where sal in (min_sal, max_sal)
MySQL和PostgreSQL
SELECT ename
FROM emp
WHERE sal IN ((SELECT MIN(sal) FROM emp), (SELECT MAX(sal) FROM emp))
11.7 查询未来行
如果有员工的工资低于紧随其后入职的同时，将其找出来。
确定“未来”的含义。我们必须对结果集排序以便能明确判断某一行记录含有的值是否晚于另一行。
DB2 MySQL ostgreSQL SQL Server
使用子查询为每一个员工计算如下的值
- 入职比他晚、且工资更高的员工当中最早入职的那个人的入职日期
- 入职比他晚的员工当中最早入职的那个人的入职日期。
如果上述两个日期相等，那么这个人就是我们要找的。
SELECT
ename,
sal,
hiredate
FROM
(
SELECT
a.ename,
a.sal,
a.hiredate,
( SELECT min( hiredate ) FROM emp b WHERE b.hiredate > a.hiredate AND b.sal > a.sal ) AS next_sal_grtr,
( SELECT min( hiredate ) FROM emp b WHERE b.hiredate > a.hiredate ) AS next_hire
FROM
emp a
) x
WHERE
next_sal_grtr = next_hire;
Oracle
可以使用窗口函数LEAD OVER访问下一个入职的员工工资。
窗口函数LEAD OVER正好可以解决这一类问题，LEAD OVER不仅使得代码更加可读性，同时也让解决方案变得更灵活，因为我们可以传递一个参数，告诉它需要往前看未来多少行的数据(默认值一行)。在排序号的数据集如果含有重复数据，那么这种情况下能够万千看多于1行的数据是很重要的。
SELECT
ename,
sal,
hiredate
FROM
(
SELECT
ename,
sal,
hiredate,
lead( sal, cnt - rn + 1 ) over ( ORDER BY hiredate ) next_sal
FROM
(
SELECT
ename,
sal,
hiredate,
count(*) over ( PARTITION BY hiredate ) cnt,
row_number() over ( PARTITION BY hiredate ORDER BY empno ) rn
FROM
emp
) b
) b
WHERE
sal < next_sal;
上述的做法关键在于找出从当前行到它应该与之比较行的正确距离。例如，如果有5个重复行，那么它的第一行就需要跳过5行数据才能找到正确的LEAD OVER行。
11.8 行值轮转
返回每个员工的姓名、工资，以及下一个最高和最低的工资值。如果没有找到更高或更低的工资值，希望结果集可以折回“第一个SAL的前一行是最后一个SAL，反之，最后一个SAL的下一行即是第一个SAL。”
Oracle
窗口函数LEAD OVER和LAG OVER使得本问题解决起来相对容易，而且代码可读性更好。对其其他数据库，可以使用标量子查询，不过Tie可能会带来问题。由于存在Tie问题，对于不支持窗口函数的关系型管理系统。只能提供一个近似解决方案。
-- 使用窗口函数 LAG OVER 和 LEAD OVER 访问当前行的上一行和下一行记录
select ename,
sal,
nvl(lead(sal) over(order by sal), min(sal) over()) forward,
nvl(lag(sal) over(order by sal), max(sal) over()) rewind
from emp;
默认情况下，窗口函数LAG OVER和LEAD OVER将分别返回当前行的上一行和下一行记录，“上一行”和“下一行”取决于OVER子句 里的ORDER BY部分，上述会首先按照SAL排序数据集，并提取出当前行的上一行和下一行
select ename,
sal,
lead(sal) over(order by sal) forward,
lag(sal) over(order by sal) rewind
from emp
-------------------------------
+--------+------+---------+--------+
| ename | sal | forward | rewind |
+--------+------+---------+--------+
| SMITH | 800 | 950 | NULL |
| JAMES | 950 | 1100 | 800 |
| ADAMS | 1100 | 1250 | 950 |
| MARTIN | 1250 | 1250 | 1100 |
| WARD | 1250 | 1300 | 1250 |
| MILLER | 1300 | 1500 | 1250 |
| TURNER | 1500 | 1600 | 1300 |
| ALLEN | 1600 | 2450 | 1500 |
| CLARK | 2450 | 2850 | 1600 |
| BLAKE | 2850 | 2975 | 2450 |
| JONES | 2975 | 3000 | 2850 |
| FORD | 3000 | 3000 | 2975 |
| SCOTT | 3000 | 5000 | 3000 |
| KING | 5000 | NULL | 3000 |
+--------+------+---------+--------+
员工 SMITH 的 REWIND 是 Null ，而 KING 的 FORWARD 也是 Null ；这是因为两个人的SAL 分别是最低值和最高值。“问题”部分提到， FORWARD 或 REWIND 若出现 Null 值，则应该“折回”。这就意味着，对于最大的 SAL ， FORWARD 值应为 EMP 表中最小的 SAL ；而对于最小的 SAL ， REWIND 值应为最大的 SAL 。没有指定分区（即 OVER 子句后面跟一对空括号）的窗口函数 MIN OVER和MAX OVER 将分别返回最大和最小的 SAL 。结果集如下所示。
+--------+------+---------+--------+
| ename | sal | forward | rewind |
+--------+------+---------+--------+
| SMITH | 800 | 950 | 5000 |
| JAMES | 950 | 1100 | 800 |
| ADAMS | 1100 | 1250 | 950 |
| WARD | 1250 | 1250 | 1100 |
| MARTIN | 1250 | 1300 | 1250 |
| MILLER | 1300 | 1500 | 1250 |
| TURNER | 1500 | 1600 | 1300 |
| ALLEN | 1600 | 2450 | 1500 |
| CLARK | 2450 | 2850 | 1600 |
| BLAKE | 2850 | 2975 | 2450 |
| JONES | 2975 | 3000 | 2850 |
| FORD | 3000 | 3000 | 2975 |
| SCOTT | 3000 | 5000 | 3000 |
| KING | 5000 | 800 | 3000 |
+--------+------+---------+--------+
LAG OVER和 LEAD OVER还有一个非常有用的功能，就是可以指定向前或者向后移动多少行，如果想往前移动3行，并且往后移动5行，可以如下
select ename,
sal,
lead(sal, 3) over(order by sal) forward,
lag(sal, 5) over(order by sal) rewind
from emp
11.9 对结果排序
对EMP 表里的工资值排序，并且允许Tie。希望返回如下的结果集
RNK SAL
--- ----------
1 800
2 950
3 1100
4 1250
4 1250
5 1300
6 1500
7 1600
8 2450
9 2850
10 2975
11 3000
11 3000
12 5000
解决方案
窗口函数使得排序变得极其简单、方便。有3种窗口函数对排序非常有用： DENSE_RANK OVER 、 ROW_NUMBER OVER 和 RANK OVER 。
-- 因为允许Tie，选择DENSE_RANK OVER。
select dense_rank() over(order by sal) rnk, sal from emp
11.10 删除重复项
SELECT job
FROM (SELECT job, row_number() over(PARTITION BY job ORDER BY job) rn
FROM emp) v
WHERE v.rn = 1;
11.11 查找骑士值
返回一个结果集，其中包括每个员工的姓名、部门、工资、入职时间以及每一个部门里最近入职的那个员工的工资。结果集如下
DEPTNO ENAME SAL HIREDATE LATEST_SAL
------ ---------- ---------- ----------- ----------
10 MILLER 1300 23-JAN-1982 1300
10 KING 5000 17-NOV-1981 1300
10 CLARK 2450 09-JUN-1981 1300
20 ADAMS 1100 12-JAN-1983 1100
20 SCOTT 3000 09-DEC-1982 1100
20 FORD 3000 03-DEC-1981 1100
20 JONES 2975 02-APR-1981 1100
20 SMITH 800 17-DEC-1980 1100
30 JAMES 950 03-DEC-1981 950
30 MARTIN 1250 28-SEP-1981 950
30 TURNER 1500 08-SEP-1981 950
30 BLAKE 2850 01-MAY-1981 950
30 WARD 1250 22-FEB-1981 950
30 ALLEN 1600 20-FEB-1981 950
Oracle
使用窗口函数MAX OVER返回每个DEPTNO对应的最高SAL值。在KEEP子句中使用函数DENSE_RANK和LAST,并按照HIRENDATE排序，为给定的DEPTNO对应的最新HIREDATE返回最高的SAL.
select deptno,
ename,
sal,
hiredate,
max(sal) keep(dense_rank last order by hiredate) over(partition by deptno) latest_sal
from emp
order by 1, 4 desc
select deptno,
ename,
sal,
hiredate,
max(sal) over(partition by deptno) latest_sal
from emp
order by 1, 4 desc
特殊注意
- 其他关于使用 LEAD OVER 处理重复项的例子（并且有更详尽、更彻底的技术细节讨论）参
见 8.7 节和 10.2 节。
- 使用窗口函数 LAG OVER 和 LEAD OVER 访问当前行的上一行和下一行记录
- 有3种窗口函数对排序非常有用： DENSE_RANK OVER 、 ROW_NUMBER OVER 和 RANK OVER 。
- CONNECT BY
若有收获，就点个赞吧
0 人点赞
