mmysql根据某个字段切割并分组,在统计

mysql根据某个字段切割并分组,再统计

1.数据库表

substring_index( substring_index( a.desired_position_code, ',', b.help_topic_id + 1 ), ',',- 1 ) code 
FROM
	tb_biz_resume a
	JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.desired_position_code ) - length( REPLACE ( a.desired_position_code, ',', '' ) ) + 1 ) 
 where 1=1
    and   a.create_time >= CONCAT('2020-10-12',' 00:00:00')
    and a.create_time <= CONCAT('2020-10-12',' 23:59:59')

然后在分组统计

select question.type_code,
chapter.code,
COUNT(DISTINCT question.id) counts
FROM
(
SELECT DISTINCT
	substring_index( substring_index( a.desired_position_code, ',', b.help_topic_id + 1 ), ',',- 1 ) code 
FROM
	tb_biz_resume a
	JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.desired_position_code ) - length( REPLACE ( a.desired_position_code, ',', '' ) ) + 1 ) 
 where 1=1
    and   a.create_time >= CONCAT('2020-10-12',' 00:00:00')
    and a.create_time <= CONCAT('2020-10-12',' 23:59:59')
)as chapter,

(select desired_position_code,id,region,type_code
from tb_biz_resume
 where 1=1
    and   create_time >= CONCAT('2020-10-12',' 00:00:00')
    and create_time <= CONCAT('2020-10-12',' 23:59:59')
) as question 


 where 1=1
and question.desired_position_code like concat('%',chapter.code,'%')
group by question.region,chapter.code ;