如何将多组季度关联表的重复SQL查询自动化为单条语句？

问题背景 #

需要查询多组遵循[年份_季度]_result与[年份_季度]_run命名规则的关联表数据，单组查询的SQL示例如下：

select 
    abc, xyz
from
    2010_Q1_result 
inner join 
    2010_Q1_run

需求覆盖2010_Q2至2024_Q1的所有季度表组合，当前只能手动修改年份、季度参数逐个执行，希望通过单条SQL或自动化脚本完成操作。

解决方案 #

根据不同数据库类型，提供两种实用方案：直接拼接UNION ALL（适用于季度数量少的场景）、动态生成SQL（适用于批量场景）。

1. 直接拼接UNION ALL（通用所有数据库） #

如果季度总数不多，直接把所有季度的查询用UNION ALL合并，简单直观，还能手动控制要包含的季度：

select abc, xyz, '2010_Q2' as period from 2010_Q2_result inner join 2010_Q2_run
union all
select abc, xyz, '2010_Q3' as period from 2010_Q3_result inner join 2010_Q3_run
union all
select abc, xyz, '2010_Q4' as period from 2010_Q4_result inner join 2010_Q4_run
-- 依次添加2011到2023年所有4个季度的语句
union all
select abc, xyz, '2023_Q4' as period from 2023_Q4_result inner join 2023_Q4_run
union all
select abc, xyz, '2024_Q1' as period from 2024_Q1_result inner join 2024_Q1_run;

添加period字段是为了区分每条数据来自哪个季度，方便后续分析。

2. 动态生成SQL（按数据库类型选择） #

MySQL/MariaDB 存储过程实现

创建存储过程自动遍历所有目标季度，生成并执行查询：

DELIMITER //
CREATE PROCEDURE BatchQueryQuarterData()
BEGIN
    DECLARE current_year INT DEFAULT 2010;
    DECLARE current_quarter INT;
    DECLARE max_quarter INT;
    DECLARE sql_text TEXT DEFAULT '';

    WHILE current_year <= 2024 DO
        -- 2024年只到Q1，其他年份到Q4
        SET max_quarter = IF(current_year = 2024, 1, 4);
        SET current_quarter = 1;
        
        WHILE current_quarter <= max_quarter DO
            -- 跳过2010_Q1
            IF NOT (current_year = 2010 AND current_quarter = 1) THEN
                SET sql_text = CONCAT(sql_text,
                    'select abc, xyz, ''', current_year, '_Q', current_quarter, ''' as period from ',
                    current_year, '_Q', current_quarter, '_result inner join ',
                    current_year, '_Q', current_quarter, '_run union all '
                );
            END IF;
            SET current_quarter = current_quarter + 1;
        END WHILE;
        SET current_year = current_year + 1;
    END WHILE;

    -- 移除最后多余的"union all "
    SET sql_text = LEFT(sql_text, LENGTH(sql_text) - 10);
    PREPARE stmt FROM sql_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取所有数据
CALL BatchQueryQuarterData();

SQL Server 动态SQL实现

用CTE生成季度列表，再拼接执行语句：

DECLARE @sql NVARCHAR(MAX) = '';

-- 生成2010_Q2到2024_Q1的季度列表
WITH TargetQuarters AS (
    SELECT
        YEAR(q_start) AS q_year,
        DATEPART(QUARTER, q_start) AS q_quarter
    FROM (
        SELECT DATEADD(QUARTER, n, '2010-04-01') AS q_start
        FROM (SELECT TOP 55 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS n FROM sys.all_columns) t
        WHERE DATEADD(QUARTER, n, '2010-04-01') <= '2024-03-31'
    ) q_dates
)

-- 拼接所有季度的查询语句
SELECT @sql = @sql + CONCAT(
    'SELECT abc, xyz, ''', q_year, '_Q', q_quarter, ''' AS period FROM ',
    q_year, '_Q', q_quarter, '_result INNER JOIN ',
    q_year, '_Q', q_quarter, '_run UNION ALL '
)
FROM TargetQuarters;

-- 移除末尾多余的UNION ALL并执行
SET @sql = LEFT(@sql, LEN(@sql) - 10);
EXEC sp_executesql @sql;

PostgreSQL 动态SQL实现

用generate_series生成季度序列，循环拼接执行：

DO $$
DECLARE
    quarter_rec RECORD;
    sql_stmt TEXT := '';
BEGIN
    -- 遍历2010_Q2到2024_Q1的所有季度
    FOR quarter_rec IN
        SELECT
            EXTRACT(YEAR FROM q_date)::INT AS q_year,
            EXTRACT(QUARTER FROM q_date)::INT AS q_quarter
        FROM generate_series('2010-04-01'::DATE, '2024-03-31'::DATE, '3 months') AS q_date
    LOOP
        -- 拼接单季度查询语句
        sql_stmt := sql_stmt || format(
            'SELECT abc, xyz, ''%s_Q%s'' AS period FROM %I INNER JOIN %I UNION ALL ',
            quarter_rec.q_year, quarter_rec.q_quarter,
            quarter_rec.q_year || '_Q' || quarter_rec.q_quarter || '_result',
            quarter_rec.q_year || '_Q' || quarter_rec.q_quarter || '_run'
        );
    END LOOP;

    -- 移除末尾多余的UNION ALL并执行
    sql_stmt := LEFT(sql_stmt, LENGTH(sql_stmt) - 10);
    EXECUTE sql_stmt;
END $$;

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