子查询示例
此主题显示了一些可以编写的子查询。示例使用 VMart 示例数据库。
单行子查询
单行子查询将与单行比较运算符（=、>=、<=、<> 和 <=>）一起使用，而且正好返回一行。
例如，以下查询在 Vmart 数据库中检索工作时间最长的员工的姓名和雇佣日期：
=> SELECT employee_key, employee_first_name, employee_last_name, hire_date
FROM employee_dimension
WHERE hire_date = (SELECT MIN(hire_date) FROM employee_dimension);
employee_key | employee_first_name | employee_last_name | hire_date
--------------+---------------------+--------------------+------------
2292 | Mary | Bauer | 1956-01-11
(1 row)
多行子查询
多行子查询返回多个记录。
例如，以下 IN 子句子查询返回了六个地区薪水最高的员工的姓名：
=> SELECT employee_first_name, employee_last_name, annual_salary, employee_region
FROM employee_dimension WHERE annual_salary IN
(SELECT MAX(annual_salary) FROM employee_dimension GROUP BY employee_region)
ORDER BY annual_salary DESC;
employee_first_name | employee_last_name | annual_salary | employee_region
---------------------+--------------------+---------------+-------------------
Alexandra | Sanchez | 992363 | West
Mark | Vogel | 983634 | South
Tiffany | Vu | 977716 | SouthWest
Barbara | Lewis | 957949 | MidWest
Sally | Gauthier | 927335 | East
Wendy | Nielson | 777037 | NorthWest
(6 rows)
多列子查询
多列子查询返回一个或多个列。有时，子查询的结果集在列对列比较和行对行比较的包含查询中进行评估。
注意
多列子查询可以使用 <>、!= 和 = 运算符，但不可以使用 <、>、<=、>= 运算符。您可以将一些多列子查询替换为联接，也可以将联接替换为多列子查询。例如，以下两个查询会检索向马萨诸塞州客户在线售出的所有产品的销售事务，然后返回了相同的结果集。唯一的区别在于，第一个查询编写为联接，第二个查询编写为子查询。
以下查询返回各地区薪水超过平均薪水的所有员工：
=> SELECT e.employee_first_name, e.employee_last_name, e.annual_salary,
e.employee_region, s.average
FROM employee_dimension e,
(SELECT employee_region, AVG(annual_salary) AS average
FROM employee_dimension GROUP BY employee_region) AS s
WHERE e.employee_region = s.employee_region AND e.annual_salary > s.average
ORDER BY annual_salary DESC;
employee_first_name | employee_last_name | annual_salary | employee_region | average
---------------------+--------------------+---------------+-----------------+------------------
Doug | Overstreet | 995533 | East | 61192.786013986
Matt | Gauthier | 988807 | South | 57337.8638902996
Lauren | Nguyen | 968625 | West | 56848.4274914089
Jack | Campbell | 963914 | West | 56848.4274914089
William | Martin | 943477 | NorthWest | 58928.2276119403
Luigi | Campbell | 939255 | MidWest | 59614.9170454545
Sarah | Brown | 901619 | South | 57337.8638902996
Craig | Goldberg | 895836 | East | 61192.786013986
Sam | Vu | 889841 | MidWest | 59614.9170454545
Luigi | Sanchez | 885078 | MidWest | 59614.9170454545
Michael | Weaver | 882685 | South | 57337.8638902996
Doug | Pavlov | 881443 | SouthWest | 57187.2510548523
Ruth | McNulty | 874897 | East | 61192.786013986
Luigi | Dobisz | 868213 | West | 56848.4274914089
Laura | Lang | 865829 | East | 61192.786013986
...
您也可以在 FROM、WHERE 和 HAVING 子句中使用 EXCEPT、INTERSECT 和 UNION [ALL] 关键字。
以下子查询返回了有关通过商店或在线销售渠道购买商品且购买金额超过 500 美元的所有康涅狄格州客户的信息：
=> SELECT DISTINCT customer_key, customer_name FROM public.customer_dimension
WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact
WHERE sales_dollar_amount > 500
UNION ALL
SELECT customer_key FROM online_sales.online_sales_fact
WHERE sales_dollar_amount > 500)
AND customer_state = 'CT';
customer_key | customer_name
--------------+------------------
200 | Carla Y. Kramer
733 | Mary Z. Vogel
931 | Lauren X. Roy
1533 | James C. Vu
2948 | Infocare
4909 | Matt Z. Winkler
5311 | John Z. Goldberg
5520 | Laura M. Martin
5623 | Daniel R. Kramer
6759 | Daniel Q. Nguyen
...
HAVING 子句子查询
HAVING 子句与 GROUP BY 子句一起使用可过滤 GROUP BY 返回的 select-list 记录。HAVING 子句子查询必须使用布尔比较运算符：=, >, <, <>, <=, >= 并采用以下格式：
SELECT <column, ...>
FROM <table>
GROUP BY <expression>
HAVING <expression>
(SELECT <column, ...>
FROM <table>
HAVING <expression>);
例如，以下语句使用 VMart 数据库，然后返回购买低脂产品的客户数量。请注意，GROUP BY 子句必不可少，因为查询使用了聚合 (COUNT)。
=> SELECT s.product_key, COUNT(s.customer_key) FROM store.store_sales_fact s
GROUP BY s.product_key HAVING s.product_key IN
(SELECT product_key FROM product_dimension WHERE diet_type = 'Low Fat');
子查询首先返回所有低脂产品的产品键，然后由外查询统计购买这些产品的客户总数量。
product_key | count
-------------+-------
15 | 2
41 | 1
66 | 1
106 | 1
118 | 1
169 | 1
181 | 2
184 | 2
186 | 2
211 | 1
229 | 1
267 | 1
289 | 1
334 | 2
336 | 1
(15 rows)