SQL需求：统计与临时表列值匹配的列数并新增统计列

需求说明 #

将产品表每行数据与临时表的attr1、attr2、attr3、attr4列逐列比对，统计匹配的列数，新增CNT_Match_cols列存储该统计结果。

测试数据 #

临时表（temp_table）

产品表（product_table）

SQL解决方案 #

SELECT 
    p.id,
    p.prod_name,
    p.attr1,
    p.attr2,
    p.attr3,
    p.attr4,
    -- 逐列判断匹配情况并累加计数
    (CASE WHEN p.attr1 = t.attr1 THEN 1 ELSE 0 END) +
    (CASE WHEN p.attr2 = t.attr2 THEN 1 ELSE 0 END) +
    (CASE WHEN p.attr3 = t.attr3 THEN 1 ELSE 0 END) +
    (CASE WHEN p.attr4 = t.attr4 THEN 1 ELSE 0 END) AS CNT_Match_cols
FROM product_table p
CROSS JOIN temp_table t;

逻辑说明 #

1. 表关联：由于临时表仅一行数据，使用CROSS JOIN让产品表的每一行都与临时表的该行建立关联。
2. 匹配判断：通过CASE表达式对每个属性列进行匹配校验，匹配则返回1，不匹配返回0。
3. 统计计数：将四个属性列的判断结果相加，得到当前行与临时表匹配的列数，存入CNT_Match_cols。

注意事项 #

如果属性列存在NULL值，直接使用=判断会导致NULL与NULL不被视为匹配。此时可改用IS NOT DISTINCT FROM（兼容PostgreSQL、SQLite等数据库）来处理NULL场景：

(CASE WHEN p.attr1 IS NOT DISTINCT FROM t.attr1 THEN 1 ELSE 0 END) +
(CASE WHEN p.attr2 IS NOT DISTINCT FROM t.attr2 THEN 1 ELSE 0 END) +
(CASE WHEN p.attr3 IS NOT DISTINCT FROM t.attr3 THEN 1 ELSE 0 END) +
(CASE WHEN p.attr4 IS NOT DISTINCT FROM t.attr4 THEN 1 ELSE 0 END) AS CNT_Match_cols

预期结果 #

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