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

1业务场景

现有NEWS_TYPE表,字段如下

2 按逗号拆分的sql语句

2.1达梦sql语句

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

复制代码

psEVEL不可更改
2.2mysql语句

1. SELECT
2.         a.id,
3.         a.name,
4.         substring_index( substring_index( a.data, ',', b.help_topic_id + 1 ), ',',- 1 ) as data
5. FROM
6.         NEWS_TYPE a
7.         JOIN mysql.help_topic b ON b.help_topic_id < (
8.                 length( a.data ) - length(
9.                 REPLACE ( a.data, ',', '' ))+ 1)

复制代码

3达到的结果

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

4.1达梦sql语句

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

复制代码

4.2mysql语句

1. SELECT
2.         newTable.NAME,
3.         newTable.DATA,
4.         count(*) AS countNum
5. FROM
6.         (
7.         SELECT
8.                 a.id,
9.                 a.NAME,
10.                 substring_index( substring_index( a.DATA, ',', b.help_topic_id + 1 ), ',',- 1 ) AS DATA
11.         FROM
12.                 NEWS_TYPE a
13.                 JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.DATA ) - length( REPLACE ( a.DATA, ',', '' ))+ 1 )
14.         ) AS newTable
15. WHERE
16.         1 = 1
17. GROUP BY
18.         newTable.NAME,
19.         newTable.DATA

复制代码

5达到的结果

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

6.1达梦sql语句

1. 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
2. distinct id,
3. regexp_substr(data, '[^,]+', 1, LEVEL) as data
4. from NEWS_TYPE
5. connect by LEVEL <= length(data) - length(regexp_replace(data. ',', '')) + 1
6. order by id
7. ) AS newTable WHERE        1 = 1 GROUP BY        newTable.NAME,        newTable.DATA                ) AS T GROUP BY        T.DATA

复制代码

6.2mysql语句

1. SELECT
2.         T.DATA,
3.         MAX( CASE T.NAME WHEN '名称1' THEN T.countNum ELSE 0 END ) AS '名称1',
4.         MAX( CASE T.NAME WHEN '名称2' THEN T.countNum ELSE 0 END ) AS '名称2',
5.         MAX( CASE T.NAME WHEN '名称3' THEN T.countNum ELSE 0 END ) AS '名称3'
6. FROM
7.         (
8.         SELECT
9.                 newTable.NAME,
10.                 newTable.DATA,
11.                 count(*) AS countNum
12.         FROM
13.                 (
14.                 SELECT
15.                         a.id,
16.                         a.NAME,
17.                         substring_index( substring_index( a.DATA, ',', b.help_topic_id + 1 ), ',',- 1 ) AS DATA
18.                 FROM
19.                         NEWS_TYPE a
20.                         JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.DATA ) - length( REPLACE ( a.DATA, ',', '' ))+ 1 )
21.                 ) AS newTable
22.         WHERE
23.                 1 = 1
24.         GROUP BY
25.                 newTable.NAME,
26.                 newTable.DATA
27.         ) AS T
28. GROUP BY
29.         T.DATA

复制代码

7达到的结果

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。