mysql count(if())用法

看一段代码

// 省份支持模糊搜索
array_walk_recursive($where, function (&$value, $key) {
            if ("province" == "{$key}" && $value) {
                $value = ['like', "%$value%"];
            }
        });

//$where['examine_time'] = [['egt', $start_time], ['elt', $end_time], 'and'];
//获取总数量是否合理 分组数量
$group_by = 'examine_point,delivery_mode';
$fields = ["province", "examine_point", "delivery_mode", "examine_point as org_id",
            "count(id) as examine_total_num",
            "count(if(is_timely = 1,true,null)) as operate_timely_count",
            "count(if(is_timely = 0,true,null)) as operate_time_out_num",
            "count(if(operate_time is not null,true,null)) as operated_count",
            "count(if(operate_time is null,true,null)) as operating_count"];
$list = $model->signProbabilityList($where, null, $fields, $group_by, $options['sort'], $options['limit']);
$total = count($model->signProbabilityList($where, null, ["id", "examine_point", "delivery_mode"], $group_by));
$list = self::fillRate($list, $options, $group_id);

$this_month = date('Y-m', strtotime($start_time));
$last_month= date('Y-m', strtotime("$start_time -1 month"));
$group_by = 'exp_province';
$fields = ["exp_province",
            "count(distinct segment_downstream, if( segment_type in (3,5) and total_year_month = '$last_month',true,null)) as last_month_segment_delivery_num",
            "count(distinct segment_downstream, if( segment_type in (3,5) and total_year_month = '$this_month',true,null)) as month_segment_delivery_num",
            "count(if(segment_type in (3,5) and total_year_month = '$this_month',true,null)) as month_segment_depart_batch_num",
            "count(if(segment_type in (3,5) and weight >= 26000,true,null)) as month_segment_batch_car_num"];
$list = $model->getRecordList($where, null, $fields, $group_by);

注意分析

count（if）

count (if(expr, v1, v2) )函数的意思是，如果表达式expr为true（expr<>0 and expr <> NULL），则if()返回的是v1，否则返回v2

如果使用count(if(expr,1,0)) 意思为

如果是expr的则返回1，否则返回0，但是因为都有数值，count对所有有值的列进行计数。

得到的结果并不是我们要的，是不正确的，说明count（if…,1,0 ）只要有值不是null都会计数，所以才会得到此结果。

只有使用count(if(expr,id,null))才是正确的

意思：如果是expr的则返回 id，否则返回null，但是只对有id值的进行计数，不会对null值计数。

同时count里面可以使用distinct去重也可以有多个条件

sum类似
sum(if(expr,1,0))fenlei_1

意思：如果满足expr的则返回1，否则返回0，对返回辅助列进行求和。