如何批量获取Task_table中所有Type1 ID的关联父ID

需求说明 #

• 输出Task_table中所有符合条件的ID及其关联父ID（父ID未做标记）
• 基于指定条件生成关联父ID建议
• 实现批量获取，无需将第一个查询结果逐个输入第二个查询

现有查询 #

1. 获取Type1相关ID的查询 #

select id 
  from Task_table 
 where date = '${date}'  
   and type = 1  
   and version >= 1 
   and status = 2;

输出结果：

id
----
123
234
456

2. 单个ID查询关联父ID的查询（以123为例） #

-- 此处{{input_id}}来自第一个查询的结果
set hivevar:input_id= {{input_id}};
-- 从Task_table中获取当前ID对应的表名
SET hivevar:table1_name = (SELECT get_json_object(conf, '$.configuration.operator.parameter.tableName') 
                          FROM Task_table 
                          WHERE date = '${date}' AND id = ${input_id} LIMIT 1);

-- 查询关联的父ID
SELECT ${input_id} As ID, id As Related_Parent_Id
FROM Task_table
WHERE date = '20231206' 
AND conf LIKE CONCAT('%', ${table1_name}, '%');

输出结果：

id , Related_Parent_id
123    789
123   893

批量查询解决方案 #

通过子查询与JOIN操作实现批量获取，无需手动逐个输入ID：

WITH type1_tasks AS (
    -- 第一步：获取所有符合条件的Type1 ID及其对应的表名
    SELECT 
        id AS task_id,
        get_json_object(conf, '$.configuration.operator.parameter.tableName') AS table_name
    FROM Task_table 
    WHERE date = '${date}'  
      AND type = 1  
      AND version >= 1 
      AND status = 2
)
-- 第二步：批量关联查询每个ID对应的父ID
SELECT 
    t.task_id AS ID,
    p.id AS Related_Parent_Id
FROM type1_tasks t
JOIN Task_table p 
    ON p.date = '20231206' 
    AND p.conf LIKE CONCAT('%', t.table_name, '%');

期望输出 #

id , Related_Parent_id
123   789
123   893
234   334
234   766
234   900
345   411

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