1业务场景

现有NEWS_TYPE表,字段如下

2 按逗号拆分的sql语句

2.1达梦sql语句

selectdistinct id,regexp_substr(data, '[^,]+', 1, LEVEL) as datafrom NEWS_TYPEconnect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1order by id

ps:LEVEL不可更改

2.2mysql语句

SELECTa.id,a.name,substring_index( substring_index( a.data, ',', b.help_topic_id + 1 ), ',',- 1 ) as dataFROMNEWS_TYPE aJOIN mysql.help_topic b ON b.help_topic_id < (length( a.data ) - length(REPLACE ( a.data, ',', '' ))+ 1)

3达到的效果

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

4.1达梦sql语句

SELECTnewTable.NAME,newTable.DATA,count(*) AS countNum FROM(selectdistinct id,regexp_substr(data, '[^,]+', 1, LEVEL) as datafrom NEWS_TYPEconnect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1order by id) AS newTable WHERE1 = 1 GROUP BYnewTable.NAME,newTable.DATA

4.2mysql语句

SELECTnewTable.NAME,newTable.DATA,count(*) AS countNum FROM(SELECTa.id,a.NAME,substring_index( substring_index( a.DATA, ',', b.help_topic_id + 1 ), ',',- 1 ) AS DATA FROMNEWS_TYPE aJOIN mysql.help_topic b ON b.help_topic_id < ( length( a.DATA ) - length( REPLACE ( a.DATA, ',', '' ))+ 1 ) ) AS newTable WHERE1 = 1 GROUP BYnewTable.NAME,newTable.DATA

5达到的效果

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

6.1达梦sql语句

SELECTT.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(SELECTnewTable.NAME,newTable.DATA,count(*) AS countNum FROM(selectdistinct id,regexp_substr(data, '[^,]+', 1, LEVEL) as datafrom NEWS_TYPEconnect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1order by id) AS newTable WHERE1 = 1 GROUP BYnewTable.NAME,newTable.DATA) AS T GROUP BYT.DATA

6.2mysql语句

SELECTT.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(SELECTnewTable.NAME,newTable.DATA,count(*) AS countNum FROM(SELECTa.id,a.NAME,substring_index( substring_index( a.DATA, ',', b.help_topic_id + 1 ), ',',- 1 ) AS DATA FROMNEWS_TYPE aJOIN mysql.help_topic b ON b.help_topic_id < ( length( a.DATA ) - length( REPLACE ( a.DATA, ',', '' ))+ 1 ) ) AS newTable WHERE1 = 1 GROUP BYnewTable.NAME,newTable.DATA ) AS T GROUP BYT.DATA

7达到的效果


参考：https://www.cnblogs.com/muphy/p/10781505.html