如何遍历两等长列表索引对应值并聚合SQL查询结果？

方案1：纯SQL实现（推荐，无需循环） #

直接把id和counter的对应关系构建成临时数据集，结合CTE一次性完成查询，比多次Union拼接更高效：

WITH id_counter_map AS (
    VALUES 
        (1, 6),
        (2, 7),
        (3, 8),
        (4, 9),
        (5, 10)
),
cte1 AS (
    SELECT t.data, t.counter
    FROM table_1 t
    JOIN id_counter_map m ON t.id = m.column1
),
cte2 AS (
    SELECT data
    FROM cte1
    JOIN id_counter_map m ON cte1.counter = m.column2
)
SELECT * FROM cte2;

如果你的SQL方言不支持VALUES子句（比如部分旧版数据库），可以用UNION ALL构建映射表：

WITH id_counter_map AS (
    SELECT 1 AS id_val, 6 AS counter_val UNION ALL
    SELECT 2, 7 UNION ALL
    SELECT 3, 8 UNION ALL
    SELECT 4, 9 UNION ALL
    SELECT 5, 10
),
cte1 AS (
    SELECT t.data, t.counter, t.id
    FROM table_1 t
    WHERE t.id IN (SELECT id_val FROM id_counter_map)
),
cte2 AS (
    SELECT c.data
    FROM cte1 c
    JOIN id_counter_map m ON c.id = m.id_val AND c.counter = m.counter_val
)
SELECT * FROM cte2;

方案2：脚本生成Union后的SQL（模拟循环逻辑） #

如果必须严格对应伪代码里的循环+Union逻辑，可用Python脚本自动生成目标SQL：

id_list = [1,2,3,4,5]
counter_list = [6,7,8,9,10]

sql_parts = []
for idx in range(len(id_list)):
    current_id = id_list[idx]
    current_counter = counter_list[idx]
    # 生成单次查询的SQL片段
    query = f"""
WITH cte1 AS (
    SELECT data, counter FROM table_1 WHERE id = {current_id}
),
cte2 AS (
    SELECT data FROM cte1 WHERE counter = {current_counter}
)
SELECT * FROM cte2
"""
    sql_parts.append(query.strip())

# 用UNION ALL连接所有片段（需去重则替换为UNION）
final_sql = " UNION ALL ".join(sql_parts)
print(final_sql)

运行脚本会生成如下格式的SQL（示例前两组）：

WITH cte1 AS (
    SELECT data, counter FROM table_1 WHERE id = 1
),
cte2 AS (
    SELECT data FROM cte1 WHERE counter = 6
)
SELECT * FROM cte2
UNION ALL
WITH cte1 AS (
    SELECT data, counter FROM table_1 WHERE id = 2
),
cte2 AS (
    SELECT data FROM cte1 WHERE counter = 7
)
SELECT * FROM cte2

注意事项 #

• 无需去重时优先用UNION ALL，性能远高于UNION
• 纯SQL方案避免了多次查询的开销，是生产环境的首选
• 脚本生成方案适合需要严格匹配循环逻辑，或需动态生成SQL的场景

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