计算每行mysql的Null变量的数量 [英] count number of Null variables per row mysql

问题描述

我们使用innoDB在Mysql中具有下表:

We have the following table in Mysql using innoDB:

id   Var1   Var2  Var 3     
1    NULL    1     2   
2    2    NULL    NULL  
3    4       2    NULL 

我们假装生成具有每行NULL变量数量的Var4:

We pretend to produce Var4 with the number of NULL variables per row:

id Var4  
1    1  
2    2  
3    1 

我尝试失败:

update db.table 
set var4 = ISNULL(var1) + ISNULL(var2) + ISNULL(var3);

有什么建议吗?

推荐答案

这是一种方法:

select id, ((var1 is null) + (var2 is null) + (var3 is null)) as var4
from table t;

MySQL将布尔值视为整数，其中true为1，false为0.您可以将它们加起来以获得总数.

MySQL treats booleans as integers, with true being 1 and false being 0. You can just add them up to get the total.

作为更新:

update table t
    set var4 = ((var1 is null) + (var2 is null) + (var3 is null));

请注意，MySQL不支持ISNULL().那更多的是SQL Server功能.但这仍然不是ANSI标准，因此通常最好使用coalesce().

As a note, MySQL doesn't support ISNULL(). That is more of a SQL Server function. But it is not ANSI standard anyway, so you are usually better off using coalesce().

这篇关于计算每行mysql的Null变量的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！