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

问题：这个很简单，不就是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 Expression("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值是不会计算的，问题就在于空字符串这里。