PostgreSQL中如何合并同表的多个SELECT查询？

嘿，你之前尝试用JOIN来实现这个需求其实走偏啦——JOIN是用来横向关联不同表或结果集的，而我们需要的是纵向堆叠多个结果集，这时候应该用UNION ALL来完成！

基础实现：生成键值对行 #

直接写三个独立的SELECT语句，每个语句把目标字段作为Value，对应的字段名（硬编码字符串）作为Field，然后用UNION ALL把它们拼起来：

SELECT 
    ItemNumber::text AS "Value",  -- 转成text确保类型统一，避免字段类型不一致报错
    'ItemNumber' AS "Field"
FROM item.items
UNION ALL
SELECT 
    ItemName AS "Value",
    'ItemName' AS "Field"
FROM item.items
UNION ALL
SELECT 
    ItemBarcode AS "Value",
    'ItemBarcode' AS "Field"
FROM item.items;

这里要注意两点：

• 如果你的ItemNumber是数字类型（比如int/bigint），一定要用::text转成字符串，不然和后面的字符串类型字段合并时会报错
• 用UNION ALL而不是UNION：UNION会自动去重（代价是性能损耗），而我们需要保留所有原始行，所以UNION ALL更合适也更快

进阶：直接生成JSON数组 #

如果想要直接得到你示例里的JSON格式输出，可以用PostgreSQL的JSON聚合函数json_agg和row_to_json来一步到位：

SELECT json_agg(row_to_json(t)) AS "items"
FROM (
    -- 这里放上面的UNION ALL查询
    SELECT 
        ItemNumber::text AS "Value",
        'ItemNumber' AS "Field"
    FROM item.items
    UNION ALL
    SELECT 
        ItemName AS "Value",
        'ItemName' AS "Field"
    FROM item.items
    UNION ALL
    SELECT 
        ItemBarcode AS "Value",
        'ItemBarcode' AS "Field"
    FROM item.items
) t;

这个查询会直接返回一个名为items的列，值就是你想要的JSON数组：

[
  {"Value": "12345677", "Field": "ItemNumber"},
  {"Value": "98776", "Field": "ItemNumber"},
  {"Value": "test77 1", "Field": "ItemName"},
  ...
]

这样就完美匹配你想要的数据集格式啦！

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