SQL题型：根据逗号拆分列_sql将一列按逗号拆分多列

例1：
表：

要实现的结果：

代码：

select a.id as hyId,substring_index(substring_index(a.ch_ry_mc, ',',b.help_topic_id + 1 ),',', - 1) AS CH_RY_ID
FROM rsgl_hygl_hyxx a
JOIN mysql.help_topic b ON b.help_topic_id < (
length(a.ch_ry_mc) - length(REPLACE(a.ch_ry_mc, ',', '')) + 1)
where a.id = '1481518766988763138'
1
2
3
4
5

例2：对于特定的某天，哪个id的value值最多。需要对value字段拆解

# 设定前一天的日期为2023-05-01
with a AS
(select a.id,substring_index(substring_index(a.value, ',',b.help_topic_id + 1),',', - 1) AS value_c
FROM table_a a
JOIN mysql.help_topic b ON b.help_topic_id < (length(a.value) - length(REPLACE(a.value, ',', '')) + 1) 
where a.dt = '2023-05-01'),
b as
(select id,count(value_c) as cv
from a
group by id)

select id,cv
from b
where cv = (select max(cv) from b )
1
2
3
4
5
6
7
8
9
10
11
12
13
14