WITH语句已经成为SQL语句必不可少的部分，Clickhouse目前的版本对CTE(Common Table Expression 公共表达式)的支持比较有限，相关的限制如下：
1.Recursive queries are not supported. 2.When subquery is used inside WITH section, it’s result should be scalar with exactly one row. 3.Expression’s results are not available in subqueries. 递归查询不支持 在WITH部分的子查询，只能返回一行数据 在子查询中表达式的结果不可用。WITH语句使用示例：
1.作为变量：
Clickhouse> with 10 as start select number from system.numbers where number <5 limit 5; WITH 10 AS start SELECT number FROM system.numbers WHERE number < 5 LIMIT 5 ┌─number─┐ │ 0 │ │ 1 │ │ 2 │ │ 3 │ │ 4 │ └────────┘2.调用函数：
WITH sum(bytes) AS s SELECT formatReadableSize(s), table FROM system.parts GROUP BY table ORDER BY s ASC ┌─formatReadableSize(s)─┬─table──────┐ │ 2.59 KiB │ part_log │ │ 22.66 KiB │ trace_log │ │ 7.13 MiB │ metric_log │ │ 107.14 MiB │ t_dict │ │ 537.52 MiB │ visits_v1 │ │ 1.18 GiB │ hits_v1 │ └───────────────────────┴────────────┘ 6 rows in set. Elapsed: 0.018 sec.3.定义子查询：
WITH ( SELECT sum(bytes) FROM system.parts WHERE active ) AS total_disk_usage SELECT (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, table FROM system.parts GROUP BY table ORDER BY table_disk_usage DESC LIMIT 10 ┌──────table_disk_usage─┬─table──────┐ │ 66.13563015490257 │ hits_v1 │ │ 29.31875240635302 │ visits_v1 │ │ 5.843957690326289 │ t_dict │ │ 0.3967229532123266 │ metric_log │ │ 0.0012069656728751568 │ trace_log │ │ 0.000137742753168703 │ part_log │ └───────────────────────┴────────────┘ 6 rows in set. Elapsed: 0.019 sec.
4.在子查询中重复使用WITH语句：
WITH round(database_disk_usage) AS database_disk_usage_v1 SELECT database, database_disk_usage, database_disk_usage_v1 FROM ( WITH ( SELECT sum(data_uncompressed_bytes) FROM system.columns ) AS total_bytes SELECT database, (sum(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC ) ┌─database─┬─database_disk_usage─┬─database_disk_usage_v1─┐ │ datasets │ 96.49589077926984 │ 96 │ │ system │ 3.5041092207301596 │ 4 │ └──────────┴─────────────────────┴────────────────────────┘ 2 rows in set. Elapsed: 0.006 sec.
FROM 字句：
Clickhouse> select WatchID from hits_v1 limit 2; SELECT WatchID FROM hits_v1 LIMIT 2 ┌─────────────WatchID─┐ │ 7043438415214026105 │ │ 4944118417295196513 │ └─────────────────────┘ 2 rows in set. Elapsed: 0.056 sec. Clickhouse> select max_watchid from(select max(WatchID) max_watchid from hits_v1); SELECT max_watchid FROM ( SELECT max(WatchID) AS max_watchid FROM hits_v1 ) ┌─────────max_watchid─┐ │ 9223371678237104442 │ └─────────────────────┘ 1 rows in set. Elapsed: 0.141 sec. Processed 8.87 million rows, 70.99 MB (63.04 million rows/s., 504.31 MB/s.) Clickhouse> select number from numbers(4); SELECT number FROM numbers(4) ┌─number─┐ │ 0 │ │ 1 │ │ 2 │ │ 3 │ └────────┘ 4 rows in set. Elapsed: 0.002 sec. clickhouse中并没有oracle中的DUAL表，但是可以用clickhouse.one替代： 示例： Clickhouse> select 2 col union all select 3 from system.one FORMAT PrettyCompactNoEscapes; SELECT 2 AS col UNION ALL SELECT 3 FROM system.one FORMAT PrettyCompactNoEscapes ┌─col─┐ │ 2 │ └─────┘ ┌─col─┐ │ 3 │ └─────┘ 2 rows in set. Elapsed: 0.003 sec. 在FROM字句之后可以配合Final修饰符使用。可以配合CollapsingMergeTree和Versioned CollapsingMergeTree 等表引起进行查询操作，以在查询过程中强制合并，由于FINAL 修饰符会降低查询性能，应该尽可能避免使用。 Clickhouse> select * from system.formats where name like '%Pretty%' format PrettyCompactNoEscapes; SELECT * FROM system.formats WHERE name LIKE '%Pretty%' FORMAT PrettyCompactNoEscapes ┌─name───────────────────┬─is_input─┬─is_output─┐ │ PrettyNoEscapes │ 0 │ 1 │ │ Pretty │ 0 │ 1 │ │ PrettySpace │ 0 │ 1 │ │ PrettyCompact │ 0 │ 1 │ │ PrettyCompactNoEscapes │ 0 │ 1 │ │ PrettySpaceNoEscapes │ 0 │ 1 │ └────────────────────────┴──────────┴───────────┘ Clickhouse> select * from system.formats where name like '%Pretty%' format PrettySpace; SELECT * FROM system.formats WHERE name LIKE '%Pretty%' FORMAT PrettySpace name is_input is_output PrettyNoEscapes 0 1 Pretty 0 1 PrettySpace 0 1 PrettyCompact 0 1 PrettyCompactNoEscapes 0 1 PrettySpaceNoEscapes 0 1 6 rows in set. Elapsed: 0.003 sec. 6 rows in set. Elapsed: 0.012 sec.
结论：
clickhouse的WITH语句相当受限制，并不好用。
参考：
https://clickhouse.tech/docs/en/sql-reference/statements/select/with/
