mysql count忽略空_MySQL count 过滤空值，使其不将空数据计入条数

首先，看如下两个查询语句：

第一种

SELECT t.id, t.meetStyle, t.title,

count(p.id) AS personCount,

count(f.id) AS fileCount

FROM meeting_info t

LEFT JOIN meeting_person p

ON t.id= p.mId

LEFT JOIN meeting_file f

ON t.id=f.mId where t.ID='info0000000234'

第二种

SELECT t.id, t.meetStyle, t.title,

count(DISTINCT p.id) AS personCount,

count(DISTINCT f.id) AS fileCount

FROM meeting_info t

LEFT JOIN meeting_person p

ON t.id= p.mId

LEFT JOIN meeting_file f

ON t.id=f.mId where t.ID='info0000000234'

这两条语句虽然只有一词之差，实际却相差千里，因为第一条语句的查询结果和如下语句查询结果相同：

SELECt f1.id,f1.MeetStyle,f1.title,f1.personCount,f2.fileCount from

(SELECT t.id, t.meetStyle, t.title,

count(p.id) AS personCount

FROM meeting_info t

LEFT JOIN meeting_person p

ON t.id= p.mId

where t.ID='info0000000234' ) f1

,

(SELECT t.id, t.meetStyle, t.title,

count(f.id) AS fileCount

FROM meeting_info t

LEFT JOIN meeting_file f

ON t.id=f.mId where t.ID='info0000000234') f2

)

由上面两种查询可以看出，count(DISTINCT p.id)具有过滤当数值为空的情况，使其不再计入总数