子查询

单行子查询

单行子查询操作符有>=、>、<=、<、<>。

-- 创建学生表并插入数据。
gaussdb=# CREATE TABLE student(
    sid VARCHAR(5),     -- 学号
    grade INT,          -- 年级
    name VARCHAR(20),   -- 姓名
    height INT          -- 身高
);
gaussdb=# INSERT INTO student VALUES ('00001',1,'Scott',135),('00002',1,'Jack',95),('00003',1,'Ben',100);
gaussdb=# INSERT INTO student VALUES ('00004',2,'Henry',115),('00005',2,'Jordan',130),('00006',2,'Bob',126);
gaussdb=# INSERT INTO student VALUES ('00007',3,'Bella',128),('00008',3,'Alicia',136);
-- 创建老师表并插入数据。
gaussdb=# CREATE TABLE teacher (
    name VARCHAR(20),    -- 教师姓名
    grade INT            -- 班级
);
-- 插入数据。
gaussdb=# INSERT INTO teacher VALUES ('Bill',1),('Sally',2),('Luke',3);

-- 查询出身高比Bella高的学生。
gaussdb=# SELECT * FROM student
WHERE height > (SELECT height FROM student WHERE name = 'Bella');
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00001 |     1 | Scott  |    135
 00005 |     2 | Jordan |    130
 00008 |     3 | Alicia |    136
(3 rows)

多行子查询

多行子查询操作符：

• in：等于列表中的任意一个。
• any：需要和单行比较符一起使用，和子查询返回的任意值比较。

• all：需要和单行比较符一起使用，和子查询返回的所有值比较。
• some：any的别名，作用相同。

示例：查询出Sally和Luke的学生

gaussdb=# SELECT * FROM student t1 WHERE t1.grade IN (
    SELECT grade FROM teacher WHERE name = 'Sally' OR name = 'Luke'
);
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00004 |     2 | Henry  |    115
 00005 |     2 | Jordan |    130
 00006 |     2 | Bob    |    126
 00007 |     3 | Bella  |    128
 00008 |     3 | Alicia |    136
(5 rows)

示例：查询出2年级比3年级任意一个人都高的学生。

gaussdb=# SELECT * FROM student
WHERE grade = 2 AND 
      height > ANY (SELECT height FROM student WHERE grade = 3);
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00005 |     2 | Jordan |    130
(1 row)

--查询结果相当于：
gaussdb=# SELECT * FROM student
WHERE grade = 2 AND 
      height > (SELECT MIN(height) FROM student WHERE grade = 3);

示例：查询出1年级比2年级所有人都高的学生。

gaussdb=# SELECT * FROM student
WHERE grade = 1 AND
      height > ALL (SELECT height FROM student WHERE grade = 2);
  sid  | grade | name  | height 
-------+-------+-------+--------
 00001 |     1 | Scott |    135
(1 row)

--查询结果相当于：
gaussdb=# SELECT * FROM student
WHERE grade = 1 AND 
      height > (SELECT MAX(height) FROM student WHERE grade = 2);

关联子查询

特点：子查询不能单独运行，是和父查询相关的。先执行父查询，再执行子查询。每执行一次父查询，子查询都要重新计算一次。

示例：查询身高大于本班级平均身高的学生。

gaussdb=# SELECT * FROM student out
WHERE height > (SELECT AVG(height) FROM student
                WHERE grade = out.grade);
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00001 |     1 | Scott  |    135
 00005 |     2 | Jordan |    130
 00006 |     2 | Bob    |    126
 00008 |     3 | Alicia |    136
(4 rows)

非关联子查询

特点：子查询先将值查询出来，再返回给外层查询。

示例：查询身高大于本班级平均身高的学生。

gaussdb=# SELECT t1.* 
FROM student t1,
     (SELECT grade, AVG(height) avg_hei FROM student GROUP BY grade) t2
WHERE t1.grade = t2.grade AND 
      t1.height > t2.avg_hei;
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00001 |     1 | Scott  |    135
 00005 |     2 | Jordan |    130
 00006 |     2 | Bob    |    126
 00008 |     3 | Alicia |    136
(4 rows)

-- 删除
gaussdb=# DROP TABLE student;
gaussdb=# DROP TABLE teacher;