如果查询返回null,count = 0 mysql
问题描述:
我有mysql数据库。如果查询返回null,count = 0 mysql
我正在运行下面的查询。
SELECT cluster,
infra_properties.property_name property,
count(*)
FROM raw_alerts,
infra_properties
WHERE infra_properties.parent_group = raw_alerts.cluster
AND cluster = 'abuse-content'
AND infra_properties.property_name = 'BigBro'
AND timestamp BETWEEN '2012-12-24 00:00:00' AND '2012-12-24 23:59:59'
GROUP BY cluster;
我得到的输出为空,但我的要求是得到计数= 0。如下所述。
+---------------+----------+----------+
| cluster | property | count(*) |
+---------------+----------+----------+
| abuse-content | BigBro | 0 |
+---------------+----------+----------+
1 row in set (0.30 sec)
答
如果2个表上没有匹配的记录,则不会返回任何行。
什么你可能需要做的是有集群和财产列额外的表,然后做一些事情,如: -
SELECT a.cluster, a.property, count(c.id)
FROM SomeExtraTable a
LEFT OUTER JOIN raw_alerts b ON a.cluster = b.cluster AND AND b.cluster = 'abuse-content'
LEFT OUTER JOIN infra_properties c
ON a.property = c.property
AND c.parent_group = b.cluster
AND c.property_name = 'BigBro'
AND c.timestamp BETWEEN '2012-12-24 00:00:00' AND '2012-12-24 23:59:59'
GROUP BY a.cluster, a.property
(我只是在猜测时间戳列是什么表)
我跑了波纹管查询,但仍然没有运气。选择群集,infra_properties.property_name属性,COALESCE(COUNT(*),0)作为从raw_alerts计数,infra_properties其中infra_properties.parent_group = raw_alerts.cluster和cluster ='abuse-content'和infra_properties.property_name ='BigBro'和时间戳之间'2013-1-24 00:00:00'和'2013-1-24 23:59:59'按群组分组; – 2013-02-22 11:17:16
你尝试过'count(cluster)'吗? – tttthomasssss 2013-02-22 11:22:39
是的,已经试过了 – 2013-02-22 11:29:25