mysql多表查询数值为空null时处理成0 | 您所在的位置:网站首页 › 两张表联查的数据在前端怎么写 › mysql多表查询数值为空null时处理成0 |
1.需求
在遇到多张表查询时,很可能查一个关联数值时,并没有这条关联记录,所以查询到的结果是null,通常需要把这个结果处理成0或者其他。如图 部门是有11个的,但是其他几个部门没有人员,就不会展示出来,sql是这么写的 SELECT u.`name`, IFNULL(COUNT(*),0) as value FROM units AS u RIGHT JOIN unit_user AS uu ON u.id = uu.unitId GROUP BY uu.unitId ORDER BY u.sort ASC; 3.解决办法 SELECT u.id,u.`name`,ifnull(uu.count_id, 0) FROM units AS u LEFT JOIN ( SELECT unitId, COUNT(id) AS count_id FROM unit_user GROUP BY unitId ) AS uu ON u.id = uu.unitId ORDER BY sort ASC;或者 SELECT u.`name`, ( SELECT COUNT(id) FROM unit_user AS r WHERE r.unitId = u.id ) AS userCount FROM units AS u LEFT JOIN unit_user AS r ON u.id = r.unitId GROUP BY u.`name` ORDER BY sort ASC;结果: |
CopyRight 2018-2019 实验室设备网 版权所有 |