关于mysql表数据行列转换方法的讲解(2)-木庄网络博客

当前第2页返回上一页

+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

列转行比行转列复杂，对于列内容是用分隔符分隔的数据，我们可以使用substring_index()函数进行分割输出，并结合笛卡尔积来实现循环。

1	`select` `a.category_id,substring_index(substring_index(a.name,',',b.category_id),',',-1)` `as` `name` `from` `option2` `as` ajoin `option2` `as` `b` `on` `b.category_id<=(length(a.name) - length(replace(a.name,',',''))+1)order` `by` `a.category_id,b.category_id;`

执行结果：

mysql> select a.category_id,substring_index(substring_index(a.name,',',b.category_id),',',-1) as name from `option2` as a    -> join `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,',',''))+1)    -> order by a.category_id,b.category_id;
+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+