23-27

23.1 标量子查询比较

找出比学生大一岁以上的老师

SELECT t1.teacher_name 
from teachers as t1
where t1.birth_date >
(select max(s1.birth_date) - INTERVAL '365' day FROM student as s1);

这个例子中，标量子查询将只运行一次，并且被优化器扫描Teachers表前缩减为一个常量值。

27.1.4 按顺序分区

 CREATE table paymenthistory(
 payment_nbr INTEGER not NULL PRIMARY key,
 paid_on_time char(1) DEFAULT 'Y' not NULL CHECK (paid_on_time in ('Y','N'))
 );
 INSERT INTO paymenthistory VALUES(1006,'Y'),
(1005,'Y'),
(1004,'N'),
(1003,'Y'),
(1002,'Y'),
(1001,'Y'),
(1000,'N');
SELECT
    ( SELECT COUNT(*) FROM paymenthistory AS H2, paymenthistory AS H3
     WHERE H3.payment_nbr = H2.payment_nbr + 1
        AND H3.paid_on_time <> H2.paid_on_time AND
         H2.payment_nbr >= H1.payment_nbr )+ 1 AS grp,
    payment_nbr,
    paid_on_time 
FROM
    paymenthistory AS h1;

为每个及时或者延迟支付组分配组号。

27.2 关系除法

关系除法。思路是利用除数表对被除数表进行分区，并生成商或结果。
这问题用一个例子可以解释清楚。假设一张表记录了飞行员以及他们可以驾驶的飞机(被除数)，有一张表记录了飞机库中的飞机(除数)，我们想要一份可以驾驶飞机库中所有飞机的飞行员名单(商)。

create  table  PilotSkills
( pilot  char ( 15 )  not  null,
plane  char ( 15 )  not  null,
primary  key  ( pilot ,  plane ));
insert  into  PilotSkills
values  ( 'Celko' ,  'Piper Cub' ),
          ( 'Higgins' ,  'B-52 Bomber' ),
          ( 'Higgins' ,  'F-14 Fighter' ),
          ( 'Higgins' ,  'Piper Cub' ),
          ( 'Jones' ,  'B-52 Bomber' ),
          ( 'Jones' ,  'F-14 Fighter' ),
          ( 'Smith' ,  'B-1 Bomber' ),
          ( 'Smith' ,  'B-52 Bomber' ),
          ( 'Smith' ,  'F-14 Fighter' ),
          ( 'Wilson' ,  'B-1 Bomber' ),
          ( 'Wilson' ,  'B-52 Bomber' ),
          ( 'Wilson' ,  'F-14 Fighter' ),
          ( 'Wilson' ,  'F-17 Fighter' );
create  table  Hangar ( plane  char ( 15 )  primary  key );
insert  into  Hangar
values  ( 'B-1 Bomber' ),
          ( 'B-52 Bomber' ),
          ( 'F-14 Fighter' );

在该例中 Smith和Wilson是两个能够驾驶飞机库中所有飞机的飞行员。Higgins和 Celko能驾驶Piper Cub飞机，但是正好没有此飞机。<br />关系除法的重要特征是除数和商的CROSS JOIN将产生与一个有效的被除数的行子集。

27.2.1 带余除法

带余数除法允许被除数表中包含的值比除数多。例如，如果一个飞行员可以驾驶比飞机库中飞机种类还要多久的飞机，这对我们是有利的，查询如下。

SELECT distinct pilot
  from PilotSkills as ps1
 WHERE not EXISTS (SELECT *
          from Hangar
         where not EXISTS (SELECT *
                  FROM PilotSkills as ps2
                 where (ps1.pilot = ps2.pilot)
                   and ps2.plane = hangar.plane))

另外一种避免三层嵌套的写法

 SELECT ps1.pilot
   FROM PilotSkills as ps1, Hangar as h1
  WHERE ps1.plane = h1.plane
  GROUP BY ps1.pilot
 HAVING count(ps1.plane) = (SELECT count(plane) FROM hangar)

上面两种存在差别，如果飞机库中没有数据，NOT EXISTS 所有飞行员都将从对空集的除法中返回。

27.2.2 精确除法

被除数表必须与除数值精确匹配。不允许有任何额外的值。

 select ps1.pilot
   FROM PilotSkills as ps1
   left outer join hangar as h1
     on ps1.plane = h1.plane
  group by ps1.pilot
 HAVING count(ps1.plane) = (SELECT count(plane) FROM hangar) 
        and count(h1.plane) = (SELECT count(plane) from hangar)

此查询规定一个飞行员必须拥有与飞机库中飞机数量相同的执照，并且这些执照应与飞机库中的飞机完全匹配，不能匹配别的东西。

27.2.4 Todd的除法

create table JobParts (job_nbr char(2), part_nbr char(2));
insert  into  JobParts
values ('j1'  'p1'    ),
( 'j1'  'p2'   ),
( 'j2'  'p2'   ),
( 'j2'  'p4'   ),
( 'j2'  'p5'   ),
( 'j3'  'p2'   ) ;

27.2.5 带JOIN的除法

SELECT SP1.Pilot 
  FROM (((SELECT plane  FROM Hangar) AS H1
         INNER JOIN
         (SELECT pilot , plane  FROM PilotSkills) AS SP1
         ON H1.plane  = SP1.plane )
       INNER JOIN (SELECT *
                     FROM PilotSkills
                    WHERE pilot  = 'Higgins') AS H2
       ON H2.plane  = H1.plane )
       GROUP BY SP1.Pilot 
HAVING COUNT(*) >= (SELECT COUNT(*)
                      FROM PilotSkills
                     WHERE pilot  = 'Higgins');
SELECT * from PilotSkills ;