SQL Notebook中如何高效处理无需保留的中间视图表？

不用创建持久化的中间视图，给你几个直接能用的方案：

1. 优先用CTE（公共表表达式/WITH子句） #

这是最常用的方式，所有中间逻辑都在同一个查询里，执行完中间的临时表（table1/2/3）就自动消失，不会在数据库里留下痕迹。把你的代码改成CTE写法如下：

WITH table1 AS (
    SELECT ... FROM ... LEFT JOIN ... ON ...
),
table2 AS (
    SELECT ... FROM table1 LEFT JOIN ... ON ...
),
table3 AS (
    SELECT ... FROM table1 LEFT JOIN ... ON ...
)
CREATE OR REPLACE VIEW database.Combined_table AS
SELECT table2.field1, table2.field2, table3.field1
FROM database.table4
LEFT JOIN table2 ON ...
LEFT JOIN table3 ON ...;

CTE的好处是逻辑分层清晰，和你原来写中间视图的思路完全一致，但不需要创建持久化对象，执行完就自动清理。

2. 使用临时视图/临时表（取决于你的数据库） #

如果需要分步调试中间结果，可以用临时对象，这类对象只在当前会话有效，会话结束后自动删除，不会留在数据库里。不同数据库语法略有差异，举个例子：

PostgreSQL（临时视图） #

CREATE TEMP VIEW table1 AS SELECT ... FROM ... LEFT JOIN ... ON ...;
CREATE TEMP VIEW table2 AS SELECT ... FROM table1 LEFT JOIN ... ON ...;
CREATE TEMP VIEW table3 AS SELECT ... FROM table1 LEFT JOIN ... ON ...;

CREATE OR REPLACE VIEW database.Combined_table AS
SELECT table2.field1, table2.field2, table3.field1
FROM database.table4
LEFT JOIN table2 ON ...
LEFT JOIN table3 ON ...;

MySQL（临时表） #

CREATE TEMPORARY TABLE table1 AS SELECT ... FROM ... LEFT JOIN ... ON ...;
CREATE TEMPORARY TABLE table2 AS SELECT ... FROM table1 LEFT JOIN ... ON ...;
CREATE TEMPORARY TABLE table3 AS SELECT ... FROM table1 LEFT JOIN ... ON ...;

CREATE OR REPLACE VIEW database.Combined_table AS
SELECT table2.field1, table2.field2, table3.field1
FROM database.table4
LEFT JOIN table2 ON ...
LEFT JOIN table3 ON ...;

3. 子查询嵌套（适合简单逻辑） #

如果中间逻辑不复杂，可以直接把中间查询嵌套到主语句里，完全不需要任何中间对象，缺点是逻辑复杂时可读性差：

CREATE OR REPLACE VIEW database.Combined_table AS
SELECT t2.field1, t2.field2, t3.field1
FROM database.table4
LEFT JOIN (
    SELECT ... FROM (
        SELECT ... FROM ... LEFT JOIN ... ON ...
    ) t1 LEFT JOIN ... ON ...
) t2 ON ...
LEFT JOIN (
    SELECT ... FROM (
        SELECT ... FROM ... LEFT JOIN ... ON ...
    ) t1 LEFT JOIN ... ON ...
) t3 ON ...;

内容的提问来源于stack exchange，提问作者dummy105234234