我想计算 mysql 中特定字段集有多少字段为空,我找到了一些示例,但它们都遍历整个表。
基本上我有8个字段,
listing_photo_1 到listing_photo_8,我想知道其中有多少个被填充。
I tried:
$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
为了澄清我期望的结果:
Listing_photo_1:已填满
Listing_photo_2:已填满
Listing_photo_3:已填满
Listing_photo_4:空
Listing_photo_5:空
Listing_photo_6:空
Listing_photo_7:空
Listing_photo_8:空`
结果应该是3
您的代码尝试计算所有字段不为空的行数。你应该使用is not null
而不仅仅是not null
.
要计算字段数量,请使用以下命令:
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'";
EDIT:
如果它们为空,请使用如下逻辑:
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';
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)