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

柔情只为你懂 2022-05-21 03:56 141阅读 0赞

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'

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

![70][]

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'

![70 1][]

没有查询到结果，但是却显示第一条记录，共一条，即出现了为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  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3ODM3Mzgy_size_16_color_FFFFFF_t_70][]

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

[70]: /images/20220521/52c48abe81164a758d4f470e02d60558.png
[70 1]: /images/20220521/1c8182c2df0b4d7bbc49f674e20c8943.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3ODM3Mzgy_size_16_color_FFFFFF_t_70]: /images/20220521/49d621b9a7b54a709a41ec5654e0b72f.png