数据库实现根据逗号将一行数据分成多行数据，和后续一些操作.

1业务场景

现有NEWS_TYPE表,字段如下

在这里插入图片描述

2 按逗号拆分的sql语句

2.1达梦sql语句

select
distinct id,
regexp_substr(data, '[^,]+', 1, LEVEL) as data
from NEWS_TYPE
connect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1
order by id

ps:LEVEL不可更改

2.2mysql语句

SELECT
	a.id,
	a.name,
	substring_index( substring_index( a.data, ',', b.help_topic_id + 1 ), ',',- 1 ) as data
FROM
	NEWS_TYPE a
	JOIN mysql.help_topic b ON b.help_topic_id < (
		length( a.data ) - length(
		REPLACE ( a.data, ',', '' ))+ 1)

3达到的效果

在这里插入图片描述

4对拆分结果进行分组统计的sql语句

4.1达梦sql语句

SELECT
	newTable.NAME,
	newTable.DATA,
	count(*) AS countNum 
FROM
	(
select
distinct id,
regexp_substr(data, '[^,]+', 1, LEVEL) as data
from NEWS_TYPE
connect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1
order by id
) AS newTable 
WHERE
	1 = 1 
GROUP BY
	newTable.NAME,
	newTable.DATA

4.2mysql语句

SELECT
	newTable.NAME,
	newTable.DATA,
	count(*) AS countNum 
FROM
	(
	SELECT
		a.id,
		a.NAME,
		substring_index( substring_index( a.DATA, ',', b.help_topic_id + 1 ), ',',- 1 ) AS DATA 
	FROM
		NEWS_TYPE a
		JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.DATA ) - length( REPLACE ( a.DATA, ',', '' ))+ 1 ) 
	) AS newTable 
WHERE
	1 = 1 
GROUP BY
	newTable.NAME,
	newTable.DATA

5达到的效果

在这里插入图片描述

6对分组结果进行行转列sql语句

6.1达梦sql语句

SELECT
	T.DATA,
	MAX( CASE T.NAME WHEN '名称1' THEN T.countNum ELSE 0 END ) AS '名称1',
	MAX( CASE T.NAME WHEN '名称2' THEN T.countNum ELSE 0 END ) AS '名称2',
	MAX( CASE T.NAME WHEN '名称3' THEN T.countNum ELSE 0 END ) AS '名称3' 
FROM
	(
SELECT
	newTable.NAME,
	newTable.DATA,
	count(*) AS countNum 
FROM
	(
select
distinct id,
regexp_substr(data, '[^,]+', 1, LEVEL) as data
from NEWS_TYPE
connect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1
order by id
) AS newTable 
WHERE
	1 = 1 
GROUP BY
	newTable.NAME,
	newTable.DATA
		) AS T 
GROUP BY
	T.DATA

6.2mysql语句

SELECT
	T.DATA,
	MAX( CASE T.NAME WHEN '名称1' THEN T.countNum ELSE 0 END ) AS '名称1',
	MAX( CASE T.NAME WHEN '名称2' THEN T.countNum ELSE 0 END ) AS '名称2',
	MAX( CASE T.NAME WHEN '名称3' THEN T.countNum ELSE 0 END ) AS '名称3' 
FROM
	(
	SELECT
		newTable.NAME,
		newTable.DATA,
		count(*) AS countNum 
	FROM
		(
		SELECT
			a.id,
			a.NAME,
			substring_index( substring_index( a.DATA, ',', b.help_topic_id + 1 ), ',',- 1 ) AS DATA 
		FROM
			NEWS_TYPE a
			JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.DATA ) - length( REPLACE ( a.DATA, ',', '' ))+ 1 ) 
		) AS newTable 
	WHERE
		1 = 1 
	GROUP BY
		newTable.NAME,
		newTable.DATA 
	) AS T 
GROUP BY
	T.DATA