MySQL:带条件值的行计数

Question

我想知道有多少用户正在登录，以及他们有多少失败和成功。

category列是varchar (前程序员的愚蠢之处)，而值只有1或0。

如果1表示成功，如果0表示失败。

$loginForced = " SELECT
                    Count(categori) AS login,                       
                    Count(CASE WHEN categori = '1' THEN categori ELSE 0 END) AS success,                        
                    Count(CASE WHEN categori = '0' THEN categori ELSE 0 END) AS fail                        
                 FROM 
                    log
                 WHERE  email = '".$personal."' "; 

Answer 1

将sum与条件一起使用：

$loginForced = "select count(`categori`) as `login`,                       
                       sum(`categori` = '1') as `success`,                        
                       sum(`categori` = '0') as `fail`                        
                from `log`
                where `email` = '".$personal."' "; 

Answer 2

您可能只想跳过CASE表达式中的ELSE：

SELECT Count(categori) AS login,                       
       Count(CASE WHEN categori = '1' THEN categori END) AS success,                        
       Count(CASE WHEN categori = '0' THEN categori END) AS fail                        
FROM log
WHERE  email = '".$personal."' "; 

COUNT考虑了所有非null值，所以使用ELSE 0基本上是没有意义的，因为0将和其他值一样被计算。