如何基于另一SQL查询结果执行动态多列单查询

你的需求本质是基于动态结果集的列转置（Pivot），因为初始查询返回的id数量不固定，静态SQL无法直接实现，必须用动态SQL生成对应查询语句。下面分主流数据库给出具体实现：

通用逻辑 #

1. 从初始查询中提取所有要作为列的id值，生成对应的列定义和查询逻辑
2. 拼接静态列（如staticValue1所在列）与动态生成的列，形成完整SQL
3. 执行这条动态生成的SQL

SQL Server 示例 #

假设初始查询为 SELECT id FROM your_source_table，需基于每个id从data_table拉取对应数据：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)

-- 生成动态列的名称与查询逻辑
SELECT @cols = STRING_AGG(QUOTENAME(id), ', ')
FROM (SELECT DISTINCT id FROM your_source_table) t

-- 拼接完整查询语句
SET @query = N'
SELECT 
    staticValue AS column1,
    ' + @cols + '
FROM (
    -- 静态值来源，可替换为实际表查询或固定值
    SELECT ''staticValue1'' AS staticValue
    UNION ALL
    SELECT ''staticValue1'' AS staticValue
) static_data
CROSS APPLY (
    SELECT 
        ' + STRING_AGG(QUOTENAME(id) + ' = (SELECT target_column FROM data_table WHERE id = ''' + id + '''), ', CHAR(13)) + '
    FROM (SELECT DISTINCT id FROM your_source_table) t
) dynamic_cols'

-- 执行动态SQL
EXEC sp_executesql @query

MySQL 示例 #

用GROUP_CONCAT生成动态列，PREPARE执行动态SQL：

-- 生成动态列定义
SET @cols = (
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
        '(SELECT target_column FROM data_table WHERE id = ''', id, ''') AS `column_', id, '`'
    ))
    FROM your_source_table
);

-- 拼接完整查询语句
SET @query = CONCAT('
SELECT 
    staticValue AS column1,
    ', @cols, '
FROM (
    SELECT ''staticValue1'' AS staticValue
    UNION ALL
    SELECT ''staticValue1'' AS staticValue
) static_data
');

-- 执行动态SQL
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 示例 #

用string_agg生成动态列，EXECUTE执行：

DO $$
DECLARE
    cols TEXT;
    query TEXT;
BEGIN
    -- 生成动态列定义
    SELECT string_agg(DISTINCT format(
        '(SELECT target_column FROM data_table WHERE id = ''%s'') AS "column_%s"', 
        id, id
    ), ', ') INTO cols
    FROM your_source_table;

    -- 拼接完整查询语句
    query := format('
        SELECT 
            staticValue AS column1,
            %s
        FROM (
            SELECT ''staticValue1'' AS staticValue
            UNION ALL
            SELECT ''staticValue1'' AS staticValue
        ) static_data
    ', cols);

    -- 执行动态SQL
    EXECUTE query;
END $$;

注意事项 #

• 替换示例中的your_source_table、data_table、target_column为实际表名和字段
• 若初始查询的id包含特殊字符，需做转义处理，避免SQL注入风险
• 动态列名可根据需求自定义，比如将column_X改成业务相关名称

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