子查询
子查询（也称为内部查询或嵌套查询）是在查询内部的查询。子查询可以用于 SELECT，FROM，WHERE 和 HAVING 子句。
子查询运算符
[ NOT ] EXISTS
该 EXISTS 操作符返回所有行，其中 相关子查询 为该行生成一个或多个匹配项。 NOT EXISTS 返回所有行，其中 相关子查询 为该行生成零个匹配项。仅支持 相关子查询。
语法
[ NOT ] 在
该 IN 运算符返回所有行，其中给定表达式的值可以在 相关子查询 的结果中找到。NOT IN 返回所有行，其中给定表达式的值不能在子查询或值列表的结果中找到。
语法
expression [NOT] IN (subquery|list-literal)
示例
使用查询查看 IN 示例
SELECT
time,
room,
temp
FROM
home
WHERE
room IN (
SELECT
DISTINCT room
FROM
home_actions
)
SELECT
time,
room,
temp
FROM
home
WHERE
room NOT IN (
SELECT
DISTINCT room
FROM
home_actions
)
使用列表字面量查看 IN 示例
SELECT
time,
room,
temp
FROM home
WHERE room IN ('Bathroom', 'Bedroom', 'Kitchen')
SELECT
time,
room,
temp
FROM home
WHERE room NOT IN ('Bathroom', 'Bedroom', 'Kitchen')
选择子查询
SELECT 子查询使用来自内层查询的返回值作为外层查询的 SELECT 列表的一部分。
SELECT 子句仅支持 标量子查询，这些子查询在每次执行内层查询时返回单个值。
返回的值可以在每一行中是唯一的。
语法
SELECT [expression1[, expression2, ..., expressionN],] (<subquery>)
SELECT 子查询可作为 JOIN 操作的替代。
示例
FROM 子句子查询
FROM 子查询返回一组结果，然后由外部查询进行查询和操作。
语法
SELECT expression1[, expression2, ..., expressionN] FROM (<subquery>)
示例
查看 FROM 子查询示例
以下查询返回每个房间的最大值的平均值。
内部查询返回每个房间每个字段的最大值。
外部查询使用内部查询的结果，并返回每个字段的平均最大值。
SELECT
AVG(max_co) AS avg_max_co,
AVG(max_hum) AS avg_max_hum,
AVG(max_temp) AS avg_max_temp
FROM
(
SELECT
room,
MAX(co) AS max_co,
MAX(hum) AS max_hum,
MAX(temp) AS max_temp
FROM
home
GROUP BY
room
)
内部查询结果
| 房间 | 最大载人量 | 最大湿度 | 最大温度 |
|---|
| 客厅 | 17 | 36.4 | 22.8 |
| 厨房 | 26 | 36.9 | 23.3 |
外部查询结果
| 平均最大CO | 平均最大湿度 | 平均最大温度 |
|---|
| 21.5 | 36.7 | 23.1 |
WHERE 子查询
WHERE 子查询 比较一个表达式与子查询的结果，并返回 true 或 false。评估为 false 或 NULL 的行会被过滤掉。WHERE 子句支持相关和不相关的子查询，以及标量和非标量的子查询（取决于谓词表达式中使用的操作符）。
语法
SELECT
expression1[, expression2, ..., expressionN]
FROM
<measurement>
WHERE
expression operator (<subquery>)
WHERE 子查询可以作为 JOIN 操作的替代方案。
示例
WHERE 子句与标量子查询
以下查询返回所有 temp 值高于所有 temp 值平均值的点。子查询返回平均 temp 值。
SELECT
*
FROM
home
WHERE
temp > (
SELECT
AVG(temp)
FROM
home
)
内部查询结果
| 平均（home.temp） |
|---|
| 22.396153846153844 |
外部查询结果
| co | 湿度 | 房间 | 温度 | 时间 |
|---|
| 0 | 36.2 | 厨房 | 23 | 2022-01-01T09:00:00Z |
| 0 | 36.1 | 厨房 | 22.7 | 2022-01-01T10:00:00Z |
| 0 | 36 | 厨房 | 22.4 | 2022-01-01T11:00:00Z |
| 0 | 36 | 厨房 | 22.5 | 2022-01-01T12:00:00Z |
| 1 | 36.5 | 厨房 | 22.8 | 2022-01-01T13:00:00Z |
| 1 | 36.3 | 厨房 | 22.8 | 2022-01-01T14:00:00Z |
| 3 | 36.2 | 厨房 | 22.7 | 2022-01-01T15:00:00Z |
| 7 | 36 | 厨房 | 22.4 | 2022-01-01T16:00:00Z |
| 9 | 36 | 厨房 | 22.7 | 2022-01-01T17:00:00Z |
| 18 | 36.9 | 厨房 | 23.3 | 2022-01-01T18:00:00Z |
| 22 | 36.6 | 厨房 | 23.1 | 2022-01-01T19:00:00Z |
| 26 | 36.5 | 厨房 | 22.7 | 2022-01-01T20:00:00Z |
| 0 | 36 | 客厅 | 22.4 | 2022-01-01T13:00:00Z |
| 4 | 36 | 客厅 | 22.4 | 2022-01-01T16:00:00Z |
| 5 | 35.9 | 客厅 | 22.6 | 2022-01-01T17:00:00Z |
| 9 | 36.2 | 客厅 | 22.8 | 2022-01-01T18:00:00Z |
| 14 | 36.3 | 客厅 | 22.5 | 2022-01-01T19:00:00Z |
WHERE 子句与非标量子查询
非标量子查询必须使用 [NOT] IN 或 [NOT] EXISTS 操作符，并且只能返回单列。返回列中的值被评估为一个列表。
以下查询返回与warn级别警报在home_actions度量中具有相同时间戳的所有点，来自home度量。
SELECT
*
FROM
home
WHERE
time IN (
SELECT
DISTINCT time
FROM
home_actions
WHERE
level = 'warn'
)
内部查询结果
| 时间 |
|---|
| 2022年01月01日 18:00:00 UTC |
| 2022-01-01T19:00:00Z |
| 2022-01-01T20:00:00Z |
外部查询结果
| co | 湿度 | 房间 | 温度 | 时间 |
|---|
| 18 | 36.9 | 厨房 | 23.3 | 2022-01-01T18:00:00Z |
| 9 | 36.2 | 客厅 | 22.8 | 2022-01-01T18:00:00Z |
| 26 | 36.5 | 厨房 | 22.7 | 2022-01-01T20:00:00Z |
| 17 | 36.4 | 客厅 | 22.2 | 2022-01-01T20:00:00Z |
| 22 | 36.6 | 厨房 | 23.1 | 2022-01-01T19:00:00Z |
| 14 | 36.3 | 客厅 | 22.5 | 2022-01-01T19:00:00Z |
HAVING 子句子查询
HAVING 子查询 比较一个使用聚合函数返回的聚合值的表达式
与子查询的结果，并返回 true 或 false。
评估为 false 或 NULL 的行从结果中过滤。
HAVING 子句支持相关和非相关子查询
以及标量和非标量子查询（这取决于谓词表达式中使用的操作符）。
语法
SELECT
aggregate_expression1[, aggregate_expression2, ..., aggregate_expressionN]
FROM
<measurement>
WHERE
<conditional_expression>
GROUP BY
column_expression1[, column_expression2, ..., column_expressionN]
HAVING
expression operator (<subquery>)
示例
HAVING 子句与标量子查询
以下查询返回所有平均 temp 值大于中位数 temp 值的两个小时的时间块。
SELECT
DATE_BIN(INTERVAL '2 hours', time) AS "2-hour block",
AVG(temp) AS avg_temp
FROM
home
GROUP BY
1
HAVING
avg_temp > (
SELECT
MEDIAN(temp)
FROM
home
)
内部查询结果
外部查询结果
| 2小时块 | 平均温度 |
|---|
| 2022-01-01T12:00:00Z | 22.475 |
| 2022-01-01T16:00:00Z | 22.525 |
| 2022-01-01T18:00:00Z | 22.925 |
| 2022-01-01T14:00:00Z | 22.525 |
HAVING 子句与非标量子查询
非标量子查询必须使用 [NOT] IN 或 [NOT] EXISTS 操作符，并且只能返回单列。返回列中的值被评估为一个列表。
以下查询返回在2小时时间窗口内的最大 co 和 temp 值，其中与时间窗口相关的 time 值也与 home_actions 测量中的警告相关。
SELECT
date_bin(INTERVAL '2 hours', time) AS "2-hour block",
max(co) AS max_co,
max(temp) as max_temp
FROM
home
GROUP BY
1,
room
HAVING
"2-hour block" IN (
SELECT
DISTINCT time
FROM
home_actions
WHERE
level = 'warn'
)
内部查询结果
| 时间 |
|---|
| 2022年01月01日 18:00:00 UTC |
| 2022-01-01T19:00:00Z |
| 2022-01-01T20:00:00Z |
外部查询结果
| 2小时块 | 最大浓度 | 最高温度 |
|---|
| 2022-01-01T18:00:00Z | 14 | 22.8 |
| 2022-01-01T18:00:00Z | 22 | 23.3 |
| 2022-01-01T20:00:00Z | 17 | 22.2 |
| 2022-01-01T20:00:00Z | 26 | 22.7 |
子查询类别
SQL 子查询可以根据子查询的行为分为以下一种或多种：
在一个相关子查询中，内部查询依赖于当前正在处理的行的值。
在下面的查询中，内查询 (SELECT temp_avg FROM weather WHERE location = home.room) 依赖于外查询 (SELECT time, room, temp FROM home) 的数据 (home.room)，因此是一个 相关 子查询。
SELECT
time,
room,
temp
FROM
home
WHERE
temp = (
SELECT
temp_avg
FROM
weather
WHERE
location = home.room
)
因为相关子查询依赖于外部查询，并通常必须为外部查询返回的每一行执行，所以相关子查询的性能较低于非相关子查询。
在一个非相关子查询中，内部查询不依赖于外部查询，并独立执行。
内部查询先执行，然后将结果传递给外部查询。
在下面的查询中，内查询 (SELECT MIN(temp_avg) FROM weather) 可以独立于外查询 (SELECT time, temp FROM home) 运行，因此是一个 非相关 子查询。
SELECT
time,
temp
FROM
home
WHERE
temp < (
SELECT
MIN(temp_avg)
FROM
weather
)
标量子查询
A 标量 子查询返回单个值（一行的一列）。如果没有返回任何行，子查询返回 NULL。
下面的示例子查询返回指定列的平均值。 这个值是一个单一的标量值。
SELECT * FROM home WHERE co > (SELECT avg(co) FROM home)
非标量子查询
一个 非标量 子查询返回 0、1 或多个行，每行可能包含 1 或多个列。对于每一列，如果没有值可返回，子查询返回 NULL。如果没有行符合条件将被返回，子查询返回 0 行。
下面的示例子查询返回列中的所有不同值。返回多个值。
SELECT * FROM home WHERE room IN (SELECT DISTINCT room FROM home_actions)
