提问人：Joehat 提问时间：6/16/2022 最后编辑：Joehat 更新时间：6/16/2022 访问量：672
PostgreSQL：从列之间的比较中排除空值
PostgreSQL: Exclude null value from comparison across columns
问：
想象一下这张表：
id col1 col2 col3 col4
1 A A C
2 B B B
3 D D
我想添加一列，告诉我行的所有非 null 值是否匹配。
因此，理想的输出是：
id col1 col2 col3 col4 is_a_match
1 A A C FALSE
2 B B B TRUE
3 D D TRUE
我试过：
select *,
case
when col1 = col2
and col2 = col3
and col3 = col4
then 'TRUE'
else 'FALSE'
end as is_a_match
from my_table
但由于 null 值，将返回所有值。false
实现上述输出的最佳方法是什么？
答：
6赞
user330315
6/16/2022
#1
您可以将列转换为行，对非重复值进行计数。这将自动忽略 NULL 值：
select t.*,
(select count(distinct x.col)
from (
values (t.col1),
(t.col2),
(t.col3),
(t.col4)
) as x(col)
where x.col is not null) = 1 as is_a_match
from the_table t
如果您不想手动列出所有列，可以使用一些 JSON 魔术将列转换为行，以便计算非重复值：
select t.*,
(select count(distinct x.val)
from jsonb_each_text(to_jsonb(t) - 'id') as x(col, val)
where x.val is not null) = 1
from the_table t
1赞
Mike Organek
6/16/2022
#2
如果列数是可变的，则在 jsonb 中翻入和翻出可以节省一些剪切和粘贴：
with pivots as (
select d.id, c.k, c.v
from de_data d
cross join lateral jsonb_each_text(to_jsonb(d)) as c(k,v)
where c.k != 'id'
and c.v is not null
), match_check as (
select id, count(distinct v) = 1 as is_a_match
from pivots
group by id
)
select d.*, mc.is_a_match
from de_data d
left join match_check mc on mc.id = d.id;
db<>fiddle 在这里
2赞
Erwin Brandstetter
6/16/2022
#3
简单的布尔逻辑 - 看起来有点笨拙，但我敢打赌这是最快的方法：
SELECT *
, COALESCE(NOT (col1 <> col2 OR col1 <> col3 OR col1 <> col4
OR col2 <> col3 OR col2 <> col4
OR col3 <> col4), true) AS is_a_match
FROM tbl;
任何非空、不相等的对都构成核心表达式。与 反转，默认为 true。trueNOTCOALESCE
或者更接近你最初的尝试，这并不遥远：
SELECT *
, CASE WHEN (col1 <> col2 OR col1 <> col3 OR col1 <> col4
OR col2 <> col3 OR col2 <> col4
OR col3 <> col4)
THEN false
ELSE true END is_a_match
FROM tbl;
db<>fiddle 在这里
无论哪种方式，关键是要反转逻辑：要忽略与 NULL 值的比较，请查找不匹配（仅适用于两个非 null 值），而不是匹配项。true
代码会随着列的增加而膨胀。但是对于满是柱子的手来说，走过所有对子是最快的。
评论