Snowflake SQL中FIELD函数报错，如何获取IN语句元素位置？

Snowflake不支持MySQL等数据库中的FIELD()函数，这就是你执行SELECT field('q', 's', 'q', 'l');时出现SQL compilation error: Unknown function FIELD错误的原因。要实现获取元素在指定列表中的位置（包括处理重复元素），可以通过以下方式实现：

解决方案：用数组展开构造带位置的目标列表 #

通过ARRAY_CONSTRUCT将目标元素列表转为数组，再用FLATTEN展开数组并获取每个元素的索引位置，最后与你的表关联即可得到期望结果：

WITH positioned_targets AS (
    SELECT 
        -- 数组索引从0开始，+1转为从1计数的位置
        INDEX + 1 AS position_of_letter_in_in_statement,
        VALUE AS letter
    FROM LATERAL FLATTEN(input => ARRAY_CONSTRUCT('q', 's', 'q', 'l'))
)
SELECT 
    pt.position_of_letter_in_in_statement,
    a.letter
FROM my_table a
JOIN positioned_targets pt ON a.letter = pt.letter
ORDER BY pt.position_of_letter_in_in_statement;

说明 #

• ARRAY_CONSTRUCT('q', 's', 'q', 'l')把你原本IN语句中的元素转为Snowflake数组
• FLATTEN函数会将数组的每个元素拆分为单独行，同时返回INDEX（元素在数组中的原始位置）
• 关联my_table时，会匹配表中letter与目标列表的元素，最终按原始列表的位置排序输出，重复元素的位置也会被正确保留（比如两个q分别对应位置1和3）

验证输出 #

执行上述查询后，会得到你期望的结果格式：

position_of_letter_in_in_statement | letter
-----------------------------------|-------
1                                  | 'q'
2                                  | 's'
3                                  | 'q'
4                                  | 'l'

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