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