记一次数据统计YII2版

需求：统计机柜仓内电池数，并从小到大排序

问题：这个很简单，不就是group by一下，count一下就出来了嘛

场景：电池字段有空字符串，有null值

出错：当电池字段为空字符串的时候 count 会统计进去，比如说一个电柜12块电池，电池字段全部为空字符串，理论上应该结果为0，实际为12

解决：

1.先上mysql语句

SELECT
    count(
    DISTINCT ifnull( bat_serial, '' ))- 1 AS bat_serial_num,
    `cab_serial`
FROM
    `cabinet`
    LEFT JOIN `battery` `pcb` ON pcb.cab_serial = cabinet.serial_num
WHERE
    `equipment_state` = 2
GROUP BY
    `cab_serial`
ORDER BY
    `bat_serial_num`

2.Yii2语法

$dataProvider->query
            ->leftJoin(Battery::tableName().' pcb','pcb.cab_serial = cabinet.serial_num')
            ->select(['cabinet.*',new [removed]"count(distinct ifnull(bat_serial,''))-1 as bat_serial_num,`cab_serial`")])
            ->groupBy('cab_serial')
            ->orderBy('bat_serial_num asc')
            ->andWhere(['equipment_state'=>2]);

结语：在这里很感谢同事的帮助，

count(DISTINCT ifnull( bat_serial, '' ))- 1 AS bat_serial_num,

关键点就是这个，将空字符串去掉，因为count null值是不会计算的，问题就在于空字符串这里。