如何将查询结果作为嵌套输入整合为可批量执行的SQL查询

单key_id查询方案 #

仅需修改参数赋值即可单次查询对应结果：

DECLARE @Target_Key_ID VARCHAR(50) = '148'; -- 此处输入需要查询的key_id

WITH Top2_Keys AS (
    SELECT 
        key_id,
        UNIQUE_KEY,
        ROW_NUMBER() OVER (PARTITION BY key_id ORDER BY START_TIME DESC) AS rn
    FROM Table_Stats s
    WHERE key_name LIKE '%V:News%'
    AND key_id = @Target_Key_ID
)
SELECT 
    t.key_id,
    t.UNIQUE_KEY,
    CAST(AVG(CAST(s.START_TIME AS FLOAT)) AS DATETIME) AS avg_start_time
FROM Top2_Keys t
JOIN Table_Stats s ON t.UNIQUE_KEY = s.UNIQUE_KEY AND s.key_name LIKE '%V:News%'
WHERE t.rn <= 2
GROUP BY t.key_id, t.UNIQUE_KEY

批量key_id查询方案 #

修改IN列表即可一次返回多个key_id的对应结果：

WITH Top2_Keys AS (
    SELECT 
        key_id,
        UNIQUE_KEY,
        ROW_NUMBER() OVER (PARTITION BY key_id ORDER BY START_TIME DESC) AS rn
    FROM Table_Stats s
    WHERE key_name LIKE '%V:News%'
    AND key_id IN ('148','149','150') -- 此处填入所有需要查询的key_id
)
SELECT 
    t.key_id,
    t.UNIQUE_KEY,
    CAST(AVG(CAST(s.START_TIME AS FLOAT)) AS DATETIME) AS avg_start_time
FROM Top2_Keys t
JOIN Table_Stats s ON t.UNIQUE_KEY = s.UNIQUE_KEY AND s.key_name LIKE '%V:News%'
WHERE t.rn <= 2
GROUP BY t.key_id, t.UNIQUE_KEY
ORDER BY t.key_id

补充说明 #

• 如果需要按key_id聚合的整体平均时间，移除查询字段和GROUP BY中的UNIQUE_KEY项即可
• 上述语法适配SQL Server，其他数据库仅需调整日期转数值、窗口函数的对应语法即可正常运行

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