如何编写基于另一表匹配情况更新表的PostgreSQL查询

需求说明 #

• 更新table1的result字段，计算规则：
  • 若t1.a的值在table2的a/b/c/d/e任意一列中存在，计1分
  • t1.b、t1.c、t1.d、t1.e字段同理，每个字段满足匹配条件就计1分
  • result最终是这5个条件的得分总和（注意：每个条件只要在table2中存在至少一次匹配就计1，不是统计所有匹配行的总和）

原查询问题 #

原查询会把table2所有行的匹配次数加起来，导致结果远大于预期；修改时给IN表达式加>0是错误的——PostgreSQL里IN返回布尔值，不能直接和数值比较，语法无效。

原查询代码：

UPDATE public.table1 AS t1 SET result = (select sum(
        CASE WHEN t1.a IN (t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END +
        CASE WHEN t1.b IN (t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END +
        CASE WHEN t1.c IN (t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END +
        CASE WHEN t1.d IN (t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END +
        CASE WHEN t1.e IN (t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END )
 FROM public.table2 AS t2 )

修改后无效的查询：

UPDATE public.table1 AS t1 SET result = (select sum(
        CASE WHEN t1.a IN (t2.a, t2.b, t2.c, t2.d, t2.e)>0 THEN 1 ELSE 0 END +
        CASE WHEN t1.b IN (t2.a, t2.b, t2.c, t2.d, t2.e)>0 THEN 1 ELSE 0 END +
        CASE WHEN t1.c IN (t2.a, t2.b, t2.c, t2.d, t2.e)>0 THEN 1 ELSE 0 END +
        CASE WHEN t1.d IN (t2.a, t2.b, t2.c, t2.d, t2.e)>0 THEN 1 ELSE 0 END +
        CASE WHEN t1.e IN (t2.a, t2.b, t2.c, t2.d, t2.e)>0 THEN 1 ELSE 0 END )
 FROM public.table2 AS t2 )

正确写法 #

核心思路：对t1的每个字段，用EXISTS子查询判断是否在table2中存在至少一次匹配，再把布尔结果转成1或0后求和。

UPDATE public.table1 AS t1
SET result = 
    (CASE WHEN EXISTS (SELECT 1 FROM public.table2 t2 WHERE t1.a IN (t2.a, t2.b, t2.c, t2.d, t2.e)) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM public.table2 t2 WHERE t1.b IN (t2.a, t2.b, t2.c, t2.d, t2.e)) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM public.table2 t2 WHERE t1.c IN (t2.a, t2.b, t2.c, t2.d, t2.e)) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM public.table2 t2 WHERE t1.d IN (t2.a, t2.b, t2.c, t2.d, t2.e)) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM public.table2 t2 WHERE t1.e IN (t2.a, t2.b, t2.c, t2.d, t2.e)) THEN 1 ELSE 0 END);

写法说明 #

• EXISTS找到第一个匹配行就停止查询，比全表扫描求和效率更高
• 每个CASE把“是否存在匹配”的布尔值转成1或0，直接相加得到最终得分
• 彻底避免了原查询中对table2所有行求和的问题，确保每个条件只计1分

优化版（减少重复扫描） #

如果table2数据量大，先把所有列的数值合并成一个集合，再判断，减少重复扫描：

WITH t2_values AS (
    SELECT unnest(ARRAY[a, b, c, d, e]) AS val FROM public.table2
)
UPDATE public.table1 AS t1
SET result = 
    (CASE WHEN EXISTS (SELECT 1 FROM t2_values WHERE val = t1.a) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM t2_values WHERE val = t1.b) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM t2_values WHERE val = t1.c) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM t2_values WHERE val = t1.d) THEN 1 ELSE 0 END) +
    (CASE WHEN EXISTS (SELECT 1 FROM t2_values WHERE val = t1.e) THEN 1 ELSE 0 END);

优化说明 #

• 用CTEt2_values把table2的a/b/c/d/e列所有值合并成单列集合
• 后续每个判断都基于这个集合，只扫描table2一次，性能更优

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