旧版PostgreSQL(8.0.2 on Redshift)数组ID交集SQL查询方案求助

针对你需要检查数组是否与目标ID集合存在至少一个匹配的需求，结合旧版Redshift的限制，提供以下几种可行方法：

方法1：字符串拼接+LIKE匹配（兼容性最高） #

通过将数组转为带边界标记的字符串，避免部分匹配风险，再逐个匹配目标ID：

SELECT *
FROM table1
WHERE ',' || array_to_string(table1.ids, ',') || ',' LIKE '%,' || 'ID_one' || ',%'
   OR ',' || array_to_string(table1.ids, ',') || ',' LIKE '%,' || 'ID_two' || ',%'
   OR ',' || array_to_string(table1.ids, ',') || ',' LIKE '%,' || 'ID_three' || ',%';

• 原理：给数组转成的字符串前后及元素间加逗号，确保匹配的是完整ID（比如避免ID_one被误匹配成ID_one2）
• 注意：如果ID本身包含逗号，需替换成其他无冲突的分隔符（如|）

方法2：UNNEST拆分数组（若版本支持） #

如果你的Redshift版本支持unnest函数，可以直接拆分数组后匹配：

SELECT DISTINCT t1.*
FROM table1 t1
JOIN unnest(t1.ids) AS arr(id)
  ON arr.id IN ('ID_one','ID_two','ID_three');

• 原理：将数组拆分为多行记录，通过JOIN匹配目标ID集合，最后用DISTINCT避免原表行被重复返回

方法3：递归CTE拆分数组（无UNNEST时使用） #

若不支持unnest，用递归CTE手动拆分数组元素：

WITH RECURSIVE array_elements AS (
    SELECT 
        t1.id AS row_id,
        t1.ids,
        1 AS idx,
        t1.ids[1] AS element
    FROM table1 t1
    WHERE array_length(t1.ids, 1) >= 1
    UNION ALL
    SELECT 
        ae.row_id,
        ae.ids,
        ae.idx + 1,
        ae.ids[ae.idx + 1] AS element
    FROM array_elements ae
    WHERE ae.idx + 1 <= array_length(ae.ids, 1)
)
SELECT DISTINCT t1.*
FROM table1 t1
JOIN array_elements ae ON t1.id = ae.row_id
WHERE ae.element IN ('ID_one','ID_two','ID_three');

• 原理：通过递归遍历数组的每个索引位置，提取元素后匹配目标ID，最后关联原表去重

内容的提问来源于stack exchange，提问作者Benedict K.