聚合后left join

试试

a表与聚合后为一个临时表根据id匹配

把你原SQL发出来，搞个图片谁想给你看，
而且哥们能把你想要什么数据说出来吗？你看看自己说了点啥

SELECT
c.company_name,
b.area_name,
IF(d.is_manager='Y',u.nick_name,NULL) AS shop_user_name,
NULLIF(COUNT(d.shop_user_id),0) AS count_person,
NULLIF(COUNT(e.material_id),0) AS count_material,
a.id,
a.shop_code,
a.shop_name,
a.area_id,
a.order_num,
a.remark,
a.status,
a.create_by,
a.create_time,
a.update_by,
a.update_time,
a.del_flag
FROM jq_shop_info AS a
LEFT JOIN jq_area_info AS b
ON a.area_id = b.id
LEFT JOIN jq_scenery_info AS c
ON c.id = b.scenery_id
LEFT JOIN (
SELECT shop_id, shop_user_id, is_manager
FROM jq_shop_user
WHERE is_manager = 'Y'
) AS d
ON a.id = d.shop_id
LEFT JOIN (
SELECT shop_id, material_id
FROM jq_shop_material
) AS e
ON a.id = e.shop_id
LEFT JOIN sys_user AS u
ON d.shop_user_id = u.user_id
GROUP BY a.id

应该是执行顺序的问题，sql的join执行优先级是比较靠前的，你count的不是a表的列，group by是在join之后执行的，如果想展示a表的所有数据，建议先group by 表a，然后再去join

根据你的描述，似乎是因为使用 GROUP BY 语句导致了聚合函数的返回结果不正确，你可以改用以下方法：
1、去掉 GROUP BY 语句，改用子查询获取需要的聚合结果：
SELECT jq_shop_info.*, agg.*
FROM jq_shop_info
LEFT JOIN jq_area_info ON jq_shop_info.area_id = jq_area_info.id
LEFT JOIN jq_scenery_info ON jq_area_info.scenery_id = jq_scenery_info.id
LEFT JOIN (
SELECT jq_shop_user.shop_id,
COUNT(DISTINCT jq_shop_user.shop_user_id) AS count_person,
COUNT(DISTINCT jq_shop_material.material_id) AS count_material,
MAX(CASE WHEN jq_shop_user.is_manager = 'Y' THEN sys_user.nick_name END) AS shop_user_name
FROM jq_shop_user
LEFT JOIN sys_user ON jq_shop_user.shop_user_id = sys_user.id
LEFT JOIN jq_shop_material ON jq_shop_user.shop_id = jq_shop_material.shop_id
GROUP BY jq_shop_user.shop_id
) agg ON jq_shop_info.id = agg.shop_id;
2、在上述 SQL 语句的基础上，如果你需要按照 jq_shop_info 表中的某些字段进行排序，则可以在最后添加 ORDER BY 语句，例如：
SELECT jq_shop_info.*, agg.*
FROM jq_shop_info
LEFT JOIN jq_area_info ON jq_shop_info.area_id = jq_area_info.id
LEFT JOIN jq_scenery_info ON jq_area_info.scenery_id = jq_scenery_info.id
LEFT JOIN (
SELECT jq_shop_user.shop_id,
COUNT(DISTINCT jq_shop_user.shop_user_id) AS count_person,
COUNT(DISTINCT jq_shop_material.material_id) AS count_material,
MAX(CASE WHEN jq_shop_user.is_manager = 'Y' THEN sys_user.nick_name END) AS shop_user_name
FROM jq_shop_user
LEFT JOIN sys_user ON jq_shop_user.shop_user_id = sys_user.id
LEFT JOIN jq_shop_material ON jq_shop_user.shop_id = jq_shop_material.shop_id
GROUP BY jq_shop_user.shop_id
) agg ON jq_shop_info.id = agg.shop_id
ORDER BY jq_shop_info.create_time DESC;
这种方法可以让你获取 jq_shop_info 表中所有数据，同时保留你原始 SQL 中用到的聚合函数的结果。需要注意的是，把查询结果与 jq_shop_info 表进行组合时，一定要使用 LEFT JOIN，如果使用 INNER JOIN 的话，会导致一些 jq_shop_info 表中存在但是没有匹配到聚合查询结果的记录被忽略掉。