PostgreSQL避免笛卡尔积：基于区间匹配为现有表添加关联列的问题

看起来你遇到的是笛卡尔积的典型问题——当你直接用from Table1, Table2这种写法时，数据库会把Table1的每一行和Table2的每一行都强制组合一遍，这就导致了大量重复行的出现。咱们来一步步解决这个问题：

问题根源分析 #

你原来的SQL用了隐式的交叉连接（逗号分隔两张表），这会生成两张表的所有行组合。虽然你加了case条件，但只是在结果里过滤出符合区间的output，其他不符合的行会显示NULL，但那些多余的行依然存在，所以才会看到重复的记录。

正确的解决方案 #

你需要的是基于区间条件的关联查询，也就是只让Table1的行和Table2中符合value在(min, max)区间的行进行匹配，而不是所有行都组合。

1. 只保留匹配到区间的行（INNER JOIN）

如果你的业务逻辑是：只有当value能找到对应的区间时才显示该行，用内连接：

SELECT 
    t1.*,
    t2.output AS new_column
FROM Table1 t1
INNER JOIN Table2 t2 
    ON t1.value > t2.min AND t1.value < t2.max; -- 和你原来的区间条件保持一致（开区间）

2. 保留所有Table1的行，无匹配时显示NULL（LEFT JOIN）

如果想保留Table1的所有行，哪怕某个value找不到对应区间（此时new_column为NULL），就用左连接：

SELECT 
    t1.*,
    t2.output AS new_column
FROM Table1 t1
LEFT JOIN Table2 t2 
    ON t1.value > t2.min AND t1.value < t2.max;

3. 处理一个value匹配多个区间的情况

如果存在某个value同时落在多个(min, max)区间里的情况（虽然你的预期结果里每个value对应一个output，但提前预防这种场景很有必要），可以用LATERAL JOIN来获取单个匹配结果，比如取第一个匹配的区间：

SELECT 
    t1.*,
    t2.output AS new_column
FROM Table1 t1
LEFT JOIN LATERAL (
    SELECT output 
    FROM Table2 
    WHERE t1.value > min AND t1.value < max
    LIMIT 1 -- 只取第一个符合条件的output
) t2 ON true;

为什么这样能解决重复行问题 #

用JOIN ... ON的方式，数据库只会保留满足区间条件的行组合，而不是生成所有可能的笛卡尔积。这样每个value只会对应到正确的output，不会出现多余的重复行，正好符合你预期的结果格式。

备注：内容来源于stack exchange，提问作者cobdmg