mysql中是否有与unnest(postgresql)等价的函数？
mysql中是否有与unnest(postgresql)等价的函数？
提问于 2022-11-24 14:54:55
我们正在研究一个sql问题，它在postgresql中有一个简单的答案，但我想知道的是，我们是否也可以在mysql中以相同的理由来解决它呢？与数据集有关的问题如下
按字母顺序对'final.txt‘中的单词进行排序，并创建一个名为'wacky.txt’的新文件。输出一列中的文件内容，另一列输出文件名“wacky.txt”。
drop table google_file_store;
create table google_file_store
(
filename varchar(40),
Contents varchar(1000)
);
insert into google_file_store values ('draft1.txt', 'The stock exchange predicts a bull market which would make many investors happy.');
insert into google_file_store values ('draft2.txt', 'The stock exchange predicts a bull market which would make many investors happy, but analysts warn of possibility of too much optimism and that in fact we are awaiting a bear market.');
insert into google_file_store values ('final.txt', 'The stock exchange predicts a bull market which would make many investors happy, but analysts warn of possibility of too much optimism and that in fact we are awaiting a bear market. As always predicting the future market is an uncertain game and all investors should follow their instincts and best practices.');
commit;提供的解决方案是
select 'wacky.txt' as filename, string_agg(x.contents, ' ')
from (
select unnest(string_to_array(lower(contents),' ')) as contents
from google_file_store
where filename='final.txt'
order by 1) x对这个问题有什么mysql问题吗？
回答 1
Stack Overflow用户
发布于 2022-11-24 18:01:38
MySQL没有数组类型，因此没有string_to_array()函数或unnest()函数。
最接近的是JSON_TABLE()，它将JSON数组转换为行。但是将空格分隔的字符串转换为JSON数组并不容易。
select j.word from google_file_store
cross join json_table(
cast(concat('["', replace(contents, ' ', '","'), '"]') as json),
'$[*]' columns (word varchar(50) path '$')) j将第一个字符串作为输入的结果：
+-----------+
| word |
+-----------+
| The |
| stock |
| exchange |
| predicts |
| a |
| bull |
| market |
| which |
| would |
| make |
| many |
| investors |
| happy. |
+-----------+阅读：
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74562637
复制相关文章
相似问题
腾讯云开发者
