我的查询没有从表中返回空值或空值

This is my query and it is not returning empty or null column values in a particular row

这是我的查询，它不返回特定行中的空或空列值

SELECT if(b2b_image1 is not null or b2b_image1='' ,"1", "0") + if(b2b_image2 is not null or b2b_image2='', "1", "0") + if(b2b_image3 is not null or b2b_image3='', "1", "0") + if(b2b_image4 is not null or b2b_image4='', "1", "0") as non_empty FROM b2b_checkin_report WHERE b2b_booking_id='105';

Here, the 4th column b2b_image4 is empty, but it is returning me 4 as output like...

在这里，第4列b2b_image4是空的，但是它返回我4作为输出…

non_empty
   4

1 个解决方案

#1

I didn't fully understand what you are trying to do. You want an indication of each column if its a null or an empty string? If so , you have a few errors :

我不完全明白你在做什么。如果是空字符串或空字符串，您希望每个列都有一个指示吗?如果是的话，你有一些错误:

SELECT if(b2b_image1 is null or b2b_image1='' ,'1', '0')
      ,if(b2b_image2 is null or b2b_image2='', '1', '0') 
      ,if(b2b_image3 is null or b2b_image3='', '1', '0') 
      ,if(b2b_image4 is null or b2b_image4='', '1', '0') 
FROM b2b_checkin_report
WHERE b2b_booking_id='105';

Or, you want to see the count of the columns that are null or empty, in that case your problem was that you used IS NOT NULL instead of IS NULL , which does exactly the opposite of what you intended :

或者，你想要看到空或空的列的计数，在这种情况下，你的问题是你使用的不是空而不是空，这与你想要的完全相反:

SELECT if(b2b_image1 is null or b2b_image1='' ,1, 0)
        + if(b2b_image2 is null or b2b_image2='', 1, 0) 
        + if(b2b_image3 is null or b2b_image3='', 1, 0) 
        + if(b2b_image4 is null or b2b_image4='', 1, 0) as empty_cnt
FROM b2b_checkin_report
WHERE b2b_booking_id='105';

Also, for string use a single quote sign ' and not double " , although you don't even need strings here, you are summing up numbers, so use numbers!.

同样，对于字符串，使用单引号“而不是双引号”，尽管您在这里甚至不需要字符串，但是您正在求和数字，所以使用数字!

#1