Snowflake中CTE使用多SELECT语句的替代方案咨询

针对你遇到的CTE无法支持多SELECT的问题，这里有几种实用的替代方法，可根据结果集的结构和业务需求选择：

1. 使用UNION ALL合并结果集（列结构一致时） #

如果结果1和结果2的列数、数据类型完全匹配，可以用UNION ALL合并两个结果集，还能添加标识列区分来源：

WITH cte1 AS (
    -- 基于CTE2、CTE3生成结果1的逻辑
    SELECT c2.id, c3.name
    FROM cte2 c2
    LEFT JOIN cte3 c3 ON c2.rel_id = c3.id
),
cte2 AS (
    -- 你的CTE2查询逻辑
    SELECT id, rel_id, value FROM table_a
),
cte3 AS (
    -- 你的CTE3查询逻辑
    SELECT id, name FROM table_b
)
-- 合并展示结果1和结果2
SELECT '结果1' AS result_type, * FROM cte1
UNION ALL
SELECT '结果2' AS result_type, c2.id, c3.name
FROM cte2 c2
LEFT JOIN cte3 c3 ON c2.rel_id = c3.id;

2. 用横向连接（LATERAL JOIN）并列展示（列结构不同时） #

如果两个结果集的列结构差异较大，想把它们的结果并列展示在同一结果集中，可以使用LATERAL JOIN：

WITH cte1 AS (
    SELECT SUM(value) AS total_value FROM table_a -- 结果1逻辑
),
cte2 AS (
    SELECT id, category FROM table_a
),
cte3 AS (
    SELECT category_id, avg_score FROM table_b
),
result2 AS (
    SELECT c2.category, c3.avg_score FROM cte2 c2 JOIN cte3 c3 ON c2.id = c3.category_id
)
-- 并列展示结果1和结果2的所有字段
SELECT r1.*, r2.*
FROM cte1 r1
CROSS JOIN LATERAL (SELECT * FROM result2) r2;

若结果集为多行，可根据业务逻辑添加关联条件，或用ROW_NUMBER()来匹配对应行。

3. 子查询嵌入SELECT列表（单行结果场景） #

如果结果1和结果2都是单行数据，可直接将它们作为子查询嵌入SELECT语句中：

WITH cte1 AS (
    SELECT COUNT(*) AS total_records FROM table_a -- 结果1（单行）
),
cte2 AS (
    SELECT id, category FROM table_a
),
cte3 AS (
    SELECT category_id, avg_score FROM table_b
)
SELECT
    (SELECT total_records FROM cte1) AS result1_total,
    (SELECT AVG(avg_score) FROM cte2 JOIN cte3 ON cte2.id = cte3.category_id) AS result2_avg;

4. 临时表存储结果（需复用场景） #

如果需要多次使用这两个结果，或结果集数据量较大，可将它们存入临时表，之后再灵活查询：

-- 将结果1存入临时表
CREATE OR REPLACE TEMP TABLE temp_result1 AS
WITH cte1 AS (...), cte2 AS (...), cte3 AS (...)
SELECT * FROM cte1;

-- 将结果2存入临时表
CREATE OR REPLACE TEMP TABLE temp_result2 AS
WITH cte2 AS (...), cte3 AS (...)
SELECT * FROM cte2 JOIN cte3 ON ...;

-- 同时查询两个临时表，比如合并展示
SELECT '结果1' AS source, * FROM temp_result1
UNION ALL
SELECT '结果2' AS source, * FROM temp_result2;

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