PostgreSQL中合并regular_fee与additional_fee表的正确SQL实现

你的问题核心是要合并两个独立的数据集，而非关联匹配行，因此FULL OUTER JOIN并不适用——它会尝试匹配两张表的行，且原语句中的WHERE条件会过滤掉无法匹配的记录（NULL值无法满足BETWEEN判断）。正确的做法是用UNION ALL分别处理两张表的记录，将另一表的相关字段置为0后再合并结果。

最终SQL语句 #

SELECT
    sellable_id,
    channel_id,
    company_id,
    customer_fee,
    subscription_fee,
    0 AS base_rate,
    0 AS store_fee,
    0 AS item_vol,
    currency_code,
    posted_date AS fee_date,
    'regular' AS fee_type -- 可选：标记费用来源，便于区分
FROM regular_fee
WHERE posted_date BETWEEN '2024-01-01' AND '2024-03-31'

UNION ALL

SELECT
    sellable_id,
    channel_id,
    company_id,
    0 AS customer_fee,
    0 AS subscription_fee,
    base_rate,
    store_fee,
    item_vol,
    currency_code,
    charge_date AS fee_date,
    'additional' AS fee_type -- 可选：标记费用来源
FROM additional_fee
WHERE charge_date BETWEEN '2024-01-01' AND '2024-03-31';

关键说明 #

1. UNION ALL的作用：直接合并两个结果集，完全保留两张表中所有符合日期条件的记录，不会尝试匹配行，完美贴合你的需求。
2. 字段置0处理：
   • 从regular_fee取数时，将base_rate、store_fee、item_vol设为0；
   • 从additional_fee取数时，将customer_fee、subscription_fee设为0；
3. 统一列结构：把posted_date和charge_date统一命名为fee_date，确保两个结果集的列数、顺序、数据类型完全匹配（UNION ALL要求严格一致）；
4. 可选的fee_type：添加该字段可直观区分每条记录的来源表，方便后续数据筛选与分析。

示例输出 #

执行后会得到包含所有6条符合条件记录的结果，结构如下：

sellable_id channel_id company_id customer_fee subscription_fee base_rate store_fee item_vol currency_code fee_date   fee_type
333         123        5555       10           5                0         0        0        usd           2024-03-27 regular
333         123        5555       10           5                0         0        0        usd           2024-03-28 regular
333         123        5555       10           5                0         0        0        usd           2024-03-29 regular
333         123        5555       0            0                2         50       20       usd           2024-03-27 additional
222         123        5555       0            0                2         52       20       usd           2024-03-28 additional
444         123        5555       0            0                2         34       20       usd           2024-03-29 additional

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