mysql 列转行union all_MySQL中的列转行 - osc_qheq8wav的个人空间 - OSCHINA - 中文开源技术交流社区...

mysql中的列转行

在工作中遇到的一个MySQL列转行的统计：

场景

用户访问app时会跳出标签选择页面让用户选择喜欢的标签，在数据库中记录的是数组样式的字符串，数据样式大致如下：

id

user_id

like_tags

create_time

1

1101

[“八卦”,“数码”,“财经”]

2020-09-01 09:19:52

2

1102

[“数码”]

2020-09-01 09:23:52

3

1103

[“数码”,“影视”]

2020-09-01 09:29:52

…

…

…

…

要统计每个标签选择的人数有多少。

分析与解决

like_tags字段存放的是用户喜好的数据，这个问题是数组的字符串，需要列转行，然后进行统计。在网上搜了一下，MySQL没有数组的操作函数和explode函数，有些需要建存储过程和函数来进行统计，但生产上权限的限制问题没法随意建存储过程，而且这个统计看上去很简单。

大致分析了一下数据，进入app选取的标签不会超过8个，最多like_tags会有6个数据，利用一个临时表可以解决问题。

在个人电脑MySQL上建表测试一下

#创建表

create table user_tags(

id int

,like_tags varchar(50)

);

#添加数据

insert into user_tags values (1,'["八卦","数码","财经"]');

insert into user_tags values (2,'["数码"]');

insert into user_tags values (3,'["数码","影视"]');

查询select * from user_tags验证插入的数据

+------+------------------------+

| id | like_tags |

+------+------------------------+

| 1 | ["八卦","数码","财经"] |

| 2 | ["数码"] |

| 3 | ["数码","影视"] |

+------+------------------------+

本来想使用一下with as建临时表，发现不支持，就直接用union all然后子查询的临时表了

SELECT

SUBSTRING_INDEX( SUBSTRING_INDEX( a.clean_like_tags, ',', b.help_id + 1 ), ',',- 1 ) AS NAME,

COUNT( 1 )

FROM

(

SELECT REPLACE

( REPLACE ( REPLACE ( like_tags, '[', '' ), ']', '' ), '"', '' ) AS clean_like_tags

FROM

user_tags

) a

LEFT JOIN (

SELECT

0 AS help_id UNION ALL

SELECT

1 UNION ALL

SELECT

2 UNION ALL

SELECT

3 UNION ALL

SELECT

4 UNION ALL

SELECT

5

) b

# 序号小于分隔符,的个数

ON b.help_id < ( LENGTH( a.clean_like_tags ) - LENGTH( REPLACE ( a.clean_like_tags, ',', '' ) ) + 1 )

GROUP BY

NAME;

结算结果

+------+------------+

| NAME | COUNT( 1 ) |

+------+------------+

| 八卦 | 1 |

| 影视 | 1 |

| 数码 | 3 |

| 财经 | 1 |

+------+------------+

小结

业务人员需要数据，本来20分钟可以做好的事情，当时花了1个多小时去处理，平时多积累一下这样快速解决问题的小技巧，提升自己的效率。