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

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

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的结果集。
此时需要我们在使用聚合函数时，在最后增加分组操作

SELECT
a.id AS “id”,
a.kind AS “kind”,
a.type AS “type”,
a.role_id AS “roleId”,
c.name AS “roleName”,
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_role c
ON a.role_id = c.id

WHERE a.del_flag = ‘0’
and a.id = -1
GROUP BY a.id

即对我们关心的主键数据进行分组，避免了产生空记录的问题