MySql:将列数据转换为行 [英] MySql : Convert Column data to row
问题描述
我在 mysql 中有一个表，如下所示.
I have a table in mysql which looks like below.
id cust_id date data
1 1 1/1/2018 a b c d e f g
2 1 2/1/2018 h I j k l m n
在这个例子中，数据列有大量的数据，像 a b c d 一样由空格分隔，我想在下面的行中显示案例
Here in this example data column is having huge data seperated by space like a b c d, I would like to show case as in row like below
id cust_id date data
1 1 1/1/2018 a
1 1 1/1/2018 b
1 1 1/1/2018 c
1 1 1/1/2018 d
2 2 2/1/2018 h
2 2 2/1/2018 i
2 2 2/1/2018 j
2 2 2/1/2018 k
我检查了几个选项，例如使用 unpivot 功能，但无法实现我的输出.提前致谢！！
I have checked few option like using unpivot function, but unable to achieve my output. Thanks in advance !!
推荐答案
select
tablename.id,
tablename.date
,SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.data, ' ', numbers.n), ' ', -1) name
from
(
SELECT @row := @row + 1 as n FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(SELECT @row:=0) r
) numbers INNER JOIN Table1 tablename
on CHAR_LENGTH(tablename.data)
-CHAR_LENGTH(REPLACE(tablename.data, ' ', ''))>=numbers.n-1
order by
id, n
检查输出链接
http://sqlfiddle.com/#!9/fa0dcb/1
解释:首先通过内部查询，即
select 0
union all
select 1
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 6
union all
select 7
union all
select 8
union all
select 9
这将生成一个包含 10 个数字的 10 行表格.
This will generate a table of 10 rows with 10 numbers.
现在另一个查询:
SELECT @row := @row + 1 as n FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t1
由于上面的查询是从下面的表 't' 和表 't1' 生成行号，表 't1' 用 ',' 分隔，这意味着它们正在生成总行的笛卡尔积.例如:t 有 10 行，t1 也有 10 行，所以笛卡尔积产生 100 行.所以@row 变量增加了 100 次，并给出了 100 行从 1 到 100 的 100 个数字.
Since above query is generating row numbers from below table 't' and table 't1' which is separated by ',' means that they are producing Cartesian product of their total rows. For example: t have 10 rows and t1 also have 10 rows so, there Cartesian product produces 100 rows. So @row variable incremented 100 times and gives 100 rows of 100 numbers from 1 to 100.
以下查询:
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.data, ' ', numbers.n), ' ', -1)
这个将一个一个地接受a b c d e f g h".
this one will take "a b c d e f g h" one by one.
例如:取 numbers.n = 1然后内部 substring_index 将找到第一个空格的索引并返回该索引之前的字符串，即 'a'然后外部 substring_index 将从结果字符串的末尾找到空格，并给出字符串中的最后一个字符，即 'a'.
For example:
take numbers.n = 1
then inner substring_index will find index of first space and will return string before that index i.e. 'a'
and then outer substring_index will find the space from the end of the resulting string and will give the last character from the string i.e. 'a'.
现在如果你取 numbers.n = 2然后内部 substring_index 将找到第一个空格的索引并返回该索引之前的字符串，即 'a b'然后外部 substring_index 将从结果字符串的末尾找到空格，并给出字符串中的最后一个字符，即 'b'
Now if you
take numbers.n = 2
then inner substring_index will find index of first space and will return string before that index i.e. 'a b'
and then outer substring_index will find the space from the end of the resulting string and will give the last character from the string i.e. 'b'
总是尝试像这样分解查询，您将能够以更简单的方式理解查询.
Always try to breakdown the query like this and you will able to understand the query in simpler way.
这篇关于MySql:将列数据转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
