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

如何将“正确”计算为同一行中符合包含字符串“正确”标准的其他列的计数？

标签： sqlpostgresql

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;