使用UNION ALL从多表创建新查找表仅返回首个查询结果如何解决

问题原因及解决方案 #

你当前使用UNION ALL是做行级合并，只会把多个查询的结果按行堆叠，不会把不同表的列合并到同一行，你遇到的只返回第一个SELECT结果的问题，以及如果要实现「汇总所有日期列到新表」的需求，对应解决方法如下：

情况1：确实需要行堆叠合并，但只返回了第一个表的数据 #

按以下步骤排查：

• 单独执行每个子SELECT语句，确认table2、table3本身有符合条件的返回数据，排除表无数据、账号无表查询权限的问题
• 检查3个子查询的对应列数据类型是否一致，比如table1的col1是DATE类型，table2的col1如果是字符串类型，部分数据库会隐式转换异常导致结果丢失
• 单独执行整个UNION ALL查询块（去掉外层CREATE TABLE部分），确认结果是否符合预期，排除写入表环节的异常
• 建议显式对齐每一列的类型，同时去掉多余括号避免部分数据库兼容问题，调整后代码如下：

CREATE TABLE datetime_test AS 
SELECT 
    CAST(col1 AS DATE) AS col1, 
    CAST(NULL AS DATE) AS col2, 
    CAST(NULL AS DATE) AS col3 
FROM table1
UNION ALL 
SELECT 
    CAST(col1 AS DATE) AS col1, 
    CAST(col2 AS DATE) AS col2, 
    CAST(col3 AS DATE) AS col3 
FROM table2
UNION ALL 
SELECT 
    CAST(col1 AS DATE) AS col1, 
    CAST(col2 AS DATE) AS col2, 
    CAST(NULL AS DATE) AS col3 
FROM table3;

情况2：需求是汇总特定schema下所有表的日期列作为lookup表的字段 #

这种场景不需要用UNION，直接查询数据库的元数据表即可，不同数据库写法参考如下：

• MySQL写法：

CREATE TABLE datetime_lookup AS
SELECT 
    TABLE_NAME 表名,
    COLUMN_NAME 日期列名,
    DATA_TYPE 数据类型
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = '替换为你的schema名称'
  AND DATA_TYPE IN ('date', 'datetime', 'timestamp');

• PostgreSQL写法：

CREATE TABLE datetime_lookup AS
SELECT 
    table_name 表名,
    column_name 日期列名,
    data_type 数据类型
FROM information_schema.columns 
WHERE table_schema = '替换为你的schema名称'
  AND data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone');

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