MySQL中按数组条件分组统计客户local数量的实现

数据表结构及数据 #

groupLocal表 #

customer表 #

需求 #

统计满足**customer.local存在于groupLocal.group数组中**的客户local数量，期望结果如下：

期望结果表 #

解决方案（以MySQL为例） #

利用JSON_CONTAINS函数判断customer.local是否存在于groupLocal.group的JSON数组中，结合分组统计得到目标结果：

SELECT 
    gl.id,
    gl.nameGroup,
    COUNT(c.local) AS `count(customer.local)`
FROM groupLocal gl
LEFT JOIN customer c ON JSON_CONTAINS(gl.group, CAST(c.local AS JSON))
GROUP BY gl.id, gl.nameGroup;

说明 #

• JSON_CONTAINS(gl.group, CAST(c.local AS JSON))：将customer.local转为JSON类型后，检查其是否包含在groupLocal.group的数组内
• LEFT JOIN确保每个分组都会被统计，即便没有匹配的客户
• GROUP BY gl.id, gl.nameGroup按分组的id和名称分组，统计每组匹配的客户数量

内容的提问来源于stack exchange，提问作者Đào Văn Mong