关于mysql中使用聚合函数结果集为空,仍显示size为1,所有元素为Null问题的解决办法

转自:https://www.2cto.com/database/201806/757632.html

1.不使用聚合函数sql:

select * from sys_role_data a

left JOIN sys_office b ON a.office_id = b.id

LEFT JOIN sys_role c on a.role_id = c.id WHERE a.del_flag = '0'

如果没有查出结果,则展示无记录

\

2.使用 聚合函数, 我使用的是group_concat函数,来将结果部分元素聚合

SELECT

a.id AS "id",

a.kind AS "kind",

a.type AS "type",

a.role_id AS "roleId",

b.name AS "officeName",

c.name AS "roleName",

a.office_id AS "officeId",

group_concat(a.data_id) AS "dataId",

group_concat(a.data_name) AS "dataName",

a.useable AS "useable",

a.del_flag AS "delFlag",

a.remarks AS "remarks",

a.create_date AS "createDate",

a.create_by AS "createBy.id",

a.update_date AS "updateDate",

a.update_by AS "updateBy.id"

FROM sys_role_data a

LEFT JOIN

sys_office b

ON a.office_id = b.id

LEFT JOIN

sys_role c

ON a.role_id = c.id

WHERE a.del_flag = '0'

\

没有查询到结果,但是却显示第一条记录,共一条,即出现了为Null的结果集。

应用在Mybatis中,返回结果为List时,会出现 List不为空,有一个元素为 all Elements are null.size为1,如果有做分页处理,则也会有问题

\

出现这种情况,则需要对其返回集进行处理,过滤掉null元素,或者将null元素设置为相应的默认值

1
2
3
4
5
sysRoleData.setPage(page);
List<sysroledata> resultList = sysRoleDataDao.findList(sysRoleData);
resultList.removeAll(Collections.singleton(null));
page.setList(resultList);
page.setCount(resultList.size());</sysroledata>

处理结果如上图所示

 

posted @ 2018-09-12 21:31  习惯沉淀  阅读(1310)  评论(0编辑  收藏  举报