如何根据静态条件从两张表中动态选择单表全量列？

核心逻辑实现 #

要根据table1中leave_till_date的总和判断返回哪张表的全量数据，需先计算总和值，再通过条件分支选择对应表。以下是不同数据库的实现示例：

MySQL 实现 #

-- 计算table1的leave_till_date总和
SET @sum_leave = (SELECT SUM(leave_till_date) FROM table1);

-- 根据总和返回对应表的所有列
SELECT * FROM table1 WHERE @sum_leave > 0
UNION ALL
SELECT * FROM table2 WHERE @sum_leave = 0;

PostgreSQL 实现 #

WITH sum_calc AS (
    SELECT SUM(leave_till_date) AS total FROM table1
)
SELECT t1.* FROM table1 t1, sum_calc sc WHERE sc.total > 0
UNION ALL
SELECT t2.* FROM table2 t2, sum_calc sc WHERE sc.total = 0;

注意：table1和table2的列结构必须完全一致（列数、数据类型、顺序匹配），否则会导致查询报错。

将查询语句存储到指定表 #

假设用于存储查询的表名为query_store，结构示例为(id INT AUTO_INCREMENT PRIMARY KEY, query_text TEXT, created_at DATETIME)，可通过以下语句将上述动态查询逻辑存入：

MySQL 存储语句 #

INSERT INTO query_store (query_text, created_at)
VALUES (
    'SET @sum_leave = (SELECT SUM(leave_till_date) FROM table1); SELECT * FROM table1 WHERE @sum_leave > 0 UNION ALL SELECT * FROM table2 WHERE @sum_leave = 0;',
    NOW()
);

PostgreSQL 存储语句 #

INSERT INTO query_store (query_text, created_at)
VALUES (
    'WITH sum_calc AS (SELECT SUM(leave_till_date) AS total FROM table1) SELECT t1.* FROM table1 t1, sum_calc sc WHERE sc.total > 0 UNION ALL SELECT t2.* FROM table2 t2, sum_calc sc WHERE sc.total = 0;',
    CURRENT_TIMESTAMP
);

系统运行时调取执行 #

系统只需从query_store表中取出query_text字段的内容，直接作为SQL语句执行即可获取目标表的全量数据。

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