sql - 如果条件匹配，则计算行中的列

Question

CREATE TABLE table
    a character varying(255),
    b character varying(255),
    c character varying(255),
    d character varying(255)
);


column | value
-------+------
a      | "Correct"
b      | "Correct"
c      | "Incorrect"
d      | "Something Else"
-------+------
correct| 2

如何计算“正确”作为同一行中满足包含字符串“正确”的条件的其他列的计数？

Answer 1

一种方法相当蛮力:

select t.*,
       ( (a = 'Correct')::int + (b = 'Correct')::int + 
         (c = 'Correct')::int + (d = 'Correct')::int
       ) as num_correct
from t;

一个更有趣的方法是使用横向连接:

select t.*, v.num_correct
from t left join lateral
     (select count(*) as num_correct
      from (values (a), (b), (c), (d)) v(x)
      where x = 'Correct'
     ) v
     on 1=1;