向VALUES子句传入ARRAY失败，如何生成含ARRAY列的结果集？

问题根源 #

你碰到的问题是因为VALUES子句仅支持直接传入字面量值，不允许包含函数调用或复杂表达式——你用的数组简写[14,37]，底层会被解析成ARRAY_CONSTRUCT(14,37)这类函数调用，而这类表达式在VALUES子句里是不被允许的，所以才会报错。

可行解决方案 #

下面几种方法都能生成包含ARRAY列的临时结果集：

方法1：用UNION ALL逐行构造 #

把每一行数据拆成单独的SELECT语句，在SELECT里直接构造数组，再用UNION ALL拼接：

SELECT 97.99 AS col1, ARRAY_CONSTRUCT(14, 37) AS col2
UNION ALL
SELECT 98.01, ARRAY_CONSTRUCT()
UNION ALL
SELECT 99.00, ARRAY_CONSTRUCT(14)

方法2：借助CTE先存基础数据再转换 #

先通过VALUES存不带数组的基础数据，再在主查询里把对应字段转换成数组，结构更清晰：

WITH base_data AS (
    SELECT * FROM (VALUES (97.99, '14,37'), (98.01, ''), (99.00, '14')) AS t(col1, arr_elements)
)
SELECT 
    col1,
    CASE 
        WHEN arr_elements = '' THEN ARRAY_CONSTRUCT()
        ELSE SPLIT_TO_ARRAY(arr_elements, ',')
    END AS col2
FROM base_data

方法3：用字符串中转数组内容（轻量场景适用） #

先在VALUES里用字符串记录数组元素，再通过数组构造函数转换：

SELECT 
    col1,
    SPLIT_TO_ARRAY(col2_str, ',') AS col2
FROM (VALUES 
    (97.99, '14,37'), 
    (98.01, ''), 
    (99.00, '14')
) AS t(col1, col2_str)

额外说明 #

不同SQL引擎对VALUES子句的表达式支持不一样，比如有些引擎允许直接写字面量数组，但像Snowflake这类会限制VALUES里的函数调用，所以改用UNION ALL或者在SELECT层处理数组是更通用的方案。

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