如何在mysql中将单行拆分为多列 [英] How to split a single row in to multiple columns in mysql

问题描述

简单地问，mysql中是否有任何功能可以将单行元素拆分为多列? 我有一个带有字段user_id, user_name, user_location.

Simply Asking, Is there any function available in mysql to split single row elements in to multiple columns ? I have a table row with the fields, user_id, user_name, user_location.

用户可以在其中添加多个位置.我使用php来定位位置并将其存储在表格中作为单行.

In this a user can add multiple locations. I am imploding the locations and storing it in a table as a single row using php.

当我在网格视图中显示用户记录时，由于通过拆分user_locations显示记录，因此出现了分页问题.因此，我需要将user_locations(从单行拆分为多列).

When i am showing the user records in a grid view, I am getting problem for pagination as i am showing the records by splitting the user_locations. So I need to split the user_locations ( single row to multiple columns).

mysql中是否有任何功能可用于按字符(％)拆分和计数记录.

Is there any function available in mysql to split and count the records by character ( % ).

例如，具有US%UK%JAPAN%CANADA

我如何将此记录分为4列. 我还需要检查计数值(4).预先感谢.

How can i split this record in to 4 columns. I need to check for the count values (4) also. thanks in advance.

推荐答案

首先规范化字符串，删除空位置并确保末尾有％:

First normalize the string, removing empty locations and making sure there's a % at the end:

select replace(concat(user_location,'%'),'%%','%') as str
from YourTable where user_id = 1

然后，我们可以用技巧来计算条目数.将'％'替换为'％'，并计算添加到字符串中的空格数.例如:

Then we can count the number of entries with a trick. Replace '%' with '% ', and count the number of spaces added to the string. For example:

select length(replace(str, '%', '% ')) - length(str)
    as LocationCount    
from (
    select replace(concat(user_location,'%'),'%%','%') as str
    from YourTable where user_id = 1
) normalized

使用substring_index，我们可以为许多位置添加列:

Using substring_index, we can add columns for a number of locations:

select length(replace(str, '%', '% ')) - length(str)
    as LocationCount    
, substring_index(substring_index(str,'%',1),'%',-1) as Loc1
, substring_index(substring_index(str,'%',2),'%',-1) as Loc2
, substring_index(substring_index(str,'%',3),'%',-1) as Loc3
from (
    select replace(concat(user_location,'%'),'%%','%') as str
    from YourTable where user_id = 1
) normalized

对于您的示例US%UK%JAPAN%CANADA，将显示:

For your example US%UK%JAPAN%CANADA, this prints:

LocationCount  Loc1    Loc2    Loc3
4              US      UK      JAPAN

因此，您可以看到它是可以做到的，但是解析字符串并不是SQL的优势之一.

So you see it can be done, but parsing strings isn't one of SQL's strengths.

这篇关于如何在mysql中将单行拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！