计数非空字段mysql

问题描述

我想计算一个特定字段集的多少字段在mysql中为空，我找到了一些示例，但它们都遍历整个表.

I want to count how many fields of a specific fieldset are empty in mysql, I've found some examples but they all go through the whole table.

基本上我有8个字段，

listing_photo_1到listing_photo_8，我想获取其中有多少被填充的计数.

listing_photo_1 to listing_photo_8, I want to get the count of how many of them are filled.

我尝试过:

$result=mysql_query("SELECT count(*) as total from listings
                       WHERE listing_photo_1 IS NOT NULL AND
                             listing_photo_2 IS NOT NULL AND
                             listing_photo_3 IS NOT NULL AND
                             listing_photo_4 IS NOT NULL AND
                             listing_photo_5 IS NOT NULL AND
                             listing_photo_6 IS NOT NULL AND
                             listing_photo_7 IS NOT NULL AND
                             listing_photo_8 IS NOT NULL AND
                             pmpid = '$pmpid'");

$data=mysql_fetch_assoc($result);
echo $data['total'];

结果为:1

为了澄清我期望的结果:

To clarify the result I am expecting:

listing_photo_1:已填充

listing_photo_1: filled

listing_photo_2:已填充

listing_photo_2: filled

listing_photo_3:已填充

listing_photo_3: filled

listing_photo_4:空

listing_photo_4: empty

listing_photo_5:空

listing_photo_5: empty

listing_photo_6:空

listing_photo_6: empty

listing_photo_7:空

listing_photo_7: empty

listing_photo_8:空`

listing_photo_8: empty`

结果应为3

推荐答案

您的代码尝试计算所有字段都不为空的行数.您应该使用is not null而不是仅仅使用not null.

You code attempts to count the number of rows where all the fields a not null. You should be using is not null rather than just not null.

要计算字段数，请使用以下方法:

To count the number of fields, use this:

SELECT sum((listing_photo_1 IS NOT NULL) +
           (listing_photo_2 IS NOT NULL) +
           (listing_photo_3 IS NOT NULL) +
           (listing_photo_4 IS NOT NULL) +
           (listing_photo_5 IS NOT NULL) +
           (listing_photo_6 IS NOT NULL) +
           (listing_photo_7 IS NOT NULL) +
           (listing_photo_8 IS NOT NULL)
          ) as total
from listings
WHERE pmpid = '$pmpid';

要计算行数:

SELECT count(*) as total
from listings
WHERE listing_photo_1 IS NOT NULL AND
      listing_photo_2 IS NOT NULL AND
      listing_photo_3 IS NOT NULL AND
      listing_photo_4 IS NOT NULL AND
      listing_photo_5 IS NOT NULL AND
      listing_photo_6 IS NOT NULL AND
      listing_photo_7 IS NOT NULL AND
      listing_photo_8 IS NOT NULL AND
      pmpid = '$pmpid'";

如果它们为空，请使用如下逻辑:

If they are blank, use logic like this:

SELECT sum((listing_photo_1 IS NOT NULL and listing_photo_1 <> '') +
           (listing_photo_2 IS NOT NULL and listing_photo_2 <> '') +
           (listing_photo_3 IS NOT NULL and listing_photo_3 <> '') +
           (listing_photo_4 IS NOT NULL and listing_photo_4 <> '') +
           (listing_photo_5 IS NOT NULL and listing_photo_5 <> '') +
           (listing_photo_6 IS NOT NULL and listing_photo_6 <> '') +
           (listing_photo_7 IS NOT NULL and listing_photo_7 <> '') +
           (listing_photo_8 IS NOT NULL and listing_photo_8 <> '')
          ) as total
from listings
WHERE pmpid = '$pmpid';

这篇关于计数非空字段mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！