SQL统计优化问题请教

Question

SELECT
	*
FROM
	(
		SELECT
			count(1) AS allCount,
			county,
			county_id
		FROM
			pf_products_standard
		GROUP BY
			county_id
	) AS a left join 
	(
		SELECT
			count(1) AS green,
			county_id
		FROM
			pf_products_standard
		WHERE
			cate_two LIKE concat('%', 601, '%')
		GROUP BY
			county_id
	) AS b on a.county_id=b.county_id LEFT JOIN 
	(
		SELECT
			count(1) AS green,
			county_id
		FROM
			pf_products_standard
		WHERE
			cate_two LIKE concat('%', 602, '%')
		GROUP BY
			county_id
	) AS c on a.county_id=c.county_id LEFT JOIN 
	(
		SELECT
			count(1) AS green,
			county_id
		FROM
			pf_products_standard
		WHERE
			cate_two LIKE concat('%', 603, '%')
		GROUP BY
			county_id
	) AS d on a.county_id=d.county_id LEFT JOIN 
	(
		SELECT
			count(1) AS green,
			county_id
		FROM
			pf_products_standard
		WHERE
			cate_two LIKE concat('%', 604, '%')
		GROUP BY
			county_id
	) AS f on a.county_id=f.county_id

我需要的值:

 

问题点:

我是进行单表不同条件进行统计,都是通过left join来进行的,我担心效率问题,所以请教大佬有没有其他优化方法

Answer 1

select
county,
county_id,
count(1) as allCount,
count(case when cate_two LIKE '%601%') then 1 else 0 end) as green1,
count(case when cate_two LIKE '%602%' then 1 else 0 end) as green2,
count(case when cate_two LIKE '%603%' then 1 else 0 end) as green3,
count(case when cate_two LIKE '%604%' then 1 else 0 end) as green4
from pf_products_standard 
group by county,county_id

试试