mysql - 如何比较列并在表中计数

Question

我正在使用mysql。我需要比较表中的列

我使用 MySQL 8.0 并且只有一个数据库

create table A
(
   serial_no           varchar(10),
   p01_pass_fail       varchar(4),
   p02_pass_fail       varchar(4),
   p03_pass_fail       varchar(4),
   p04_pass_fail       varchar(4),
   p05_pass_fail       varchar(4)
)

如果一行是：

serial_no = 001,
p01_pass_fail = pass,
p02_pass_fail = fail,
p03_pass_fail = pass,
p04_pass_fail = pass,
p05_pass_fail = fail

我的完美结果是通过计数 = 3，失败计数 = 2

这对我来说并不容易

标签： mysqlsql

Answer 1

您可以使用CASE ... ENDto “translate”'pass'或'fail'to 1or 0and+来总结它。

SELECT serial_no,
       CASE p01_pass_fail
         WHEN 'fail' THEN
           0
         WHEN 'pass' THEN
           1
       END
       +
       ...
       +
       CASE p05_pass_fail
         WHEN 'fail' THEN
           0
         WHEN 'pass' THEN
           1
       END pass_count,
       CASE p01_pass_fail
         WHEN 'pass' THEN
           0
         WHEN 'fail' THEN
           1
       END
       +
       ...
       +
       CASE p05_pass_fail
         WHEN 'pass' THEN
           0
         WHEN 'fail' THEN
           1
       END fail_count
       FROM a;