公用表表达式（CTE）
2026/4/23大约 3 分钟
公用表表达式（CTE）
1. 概述
CTE（Common Table Expressions，公用表表达式）功能支持通过 WITH 子句定义一个或多个临时结果集（即公用表），这些结果集可以在同一个查询的后续部分中被多次引用。CTE 提供了一种清晰的方式来构建复杂的查询，使 SQL 代码更易读和维护。
注意：该功能从 V2.0.9-beta 版本开始提供。
2. 语法定义
CTE 的简化 SQL 语法如下：
with_clause:
WITH cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...- 支持简单 CTE 和嵌套 CTE：可以在
WITH子句中定义一个或多个 CTE，且 CTE 之间可以嵌套引用（但不能前向引用，即不能使用尚未定义的 CTE）。 - CTE 名称与源表重名：如果 CTE 名称与源表重名，在外层作用域中只有 CTE 可见，源表将被屏蔽。
- CTE 的多次引用：同一个 CTE 在外层查询中可以被多次引用。
- Explain / ExplainAnalyze 支持：支持对整个查询进行
Explain或ExplainAnalyze，但不支持对 CTE 定义中的subquery进行Explain或ExplainAnalyze。 - 列名指定限制：CTE 定义时指定的列名个数需与
subquery输出列个数一致，否则报错。 - 未使用的 CTE：如果定义的 CTE 在查询主体中没有用到，查询仍可正常执行。
3. 使用示例
基于示例数据 中的表 table1 和 table2作为源表：
3.1 简单 CTE
WITH cte1 AS (SELECT device_id, temperature FROM table1 WHERE temperature IS NOT NULL),
cte2 AS (SELECT device_id, humidity FROM table2 WHERE humidity IS NOT NULL)
SELECT * FROM cte1 join cte2 on cte1.device_id = cte2.device_id limit 10;执行结果
+---------+-----------+---------+--------+
|device_id|temperature|device_id|humidity|
+---------+-----------+---------+--------+
| 100| 90.0| 100| 45.1|
| 100| 90.0| 100| 35.2|
| 100| 90.0| 100| 35.1|
| 100| 85.0| 100| 45.1|
| 100| 85.0| 100| 35.2|
| 100| 85.0| 100| 35.1|
| 100| 85.0| 100| 45.1|
| 100| 85.0| 100| 35.2|
| 100| 85.0| 100| 35.1|
| 100| 88.0| 100| 45.1|
+---------+-----------+---------+--------+
Total line number = 10
It costs 0.075s3.2 CTE 与源表重名
WITH table1 AS (SELECT time, device_id, temperature FROM table1 WHERE temperature IS NOT NULL)
SELECT * FROM table1 limit 5;执行结果
+-----------------------------+---------+-----------+
| time|device_id|temperature|
+-----------------------------+---------+-----------+
|2024-11-30T09:30:00.000+08:00| 101| 90.0|
|2024-11-30T14:30:00.000+08:00| 101| 90.0|
|2024-11-29T10:00:00.000+08:00| 101| 85.0|
|2024-11-27T16:39:00.000+08:00| 101| 85.0|
|2024-11-27T16:40:00.000+08:00| 101| 85.0|
+-----------------------------+---------+-----------+
Total line number = 5
It costs 0.103s3.3 嵌套 CTE
WITH
table1 AS (select device_id, temperature from table1 WHERE temperature IS NOT NULL),
cte1 AS (select device_id, temperature from table2 WHERE temperature IS NOT NULL),
table2 AS (select temperature from table1),
cte2 AS (SELECT temperature FROM table1)
SELECT * FROM table2;执行结果
+-----------+
|temperature|
+-----------+
| 90.0|
| 90.0|
| 85.0|
| 85.0|
| 85.0|
| 85.0|
| 90.0|
| 85.0|
| 85.0|
| 88.0|
| 90.0|
| 90.0|
+-----------+
Total line number = 12
It costs 0.050s- 不支持前向引用
WITH
cte2 AS (SELECT temperature FROM cte1),
cte1 AS (select device_id, temperature from table1)
SELECT * FROM cte2;错误信息
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 550: Table 'database1.cte1' does not exist.3.4 CTE 的多次引用
WITH cte AS (select device_id, temperature from table1 WHERE temperature IS NOT NULL)
SELECT * FROM cte WHERE temperature > (SELECT avg(temperature ) FROM cte);执行结果
+---------+-----------+
|device_id|temperature|
+---------+-----------+
| 101| 90.0|
| 101| 90.0|
| 100| 90.0|
| 100| 88.0|
| 100| 90.0|
| 100| 90.0|
+---------+-----------+
Total line number = 6
It costs 0.241s3.5 Explain 支持
- 支持整个查询
EXPLAIN WITH cte AS (SELECT * FROM table1) SELECT * FROM cte;执行结果
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ |
| │OutputNode-7 │ |
| │OutputColumns-[time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time] │ |
| │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ |
| └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ |
| │ |
| │ |
| ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ |
| │Collect-42 │ |
| │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ |
| └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ |
| ┌───────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────┐ |
| │ │ |
| ┌───────────┐ ┌───────────┐ |
| │Exchange-49│ │Exchange-50│ |
| └───────────┘ └───────────┘ |
| │ │ |
| │ │ |
|┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│DeviceTableScanNode-41 │ │DeviceTableScanNode-40 │|
|│QualifiedTableName: database1.table1 │ │QualifiedTableName: database1.table1 │|
|│OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│|
|│DeviceNumber: 3 │ │DeviceNumber: 3 │|
|│ScanOrder: ASC │ │ScanOrder: ASC │|
|│PushDownOffset: 0 │ │PushDownOffset: 0 │|
|│PushDownLimit: 0 │ │PushDownLimit: 0 │|
|│PushDownLimitToEachDevice: false │ │PushDownLimitToEachDevice: false │|
|│RegionId: 2 │ │RegionId: 1 │|
|└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Total line number = 29
It costs 0.065s- 不支持 cte 内部查询
WITH cte AS (EXPLAIN SELECT * FROM table1) SELECT * FROM cte;错误信息
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 700: line 1:14: mismatched input 'EXPLAIN'. Expecting: <query>