我有一个表字段存储的数据类似这样:
fashion scarf
mens plaid scarf
cashmere scarf for men
mens grey scarves
mens grey scarf我需要取出这个字段值的集合(无重复的单词),返回一个字符串,结果是:mens fashion plaid grey scarves cashmere scarf for men(单词顺序无所谓)请问sql语句要怎么写呀?谢谢大家!!!
fashion scarf
mens plaid scarf
cashmere scarf for men
mens grey scarves
mens grey scarf我需要取出这个字段值的集合(无重复的单词),返回一个字符串,结果是:mens fashion plaid grey scarves cashmere scarf for men(单词顺序无所谓)请问sql语句要怎么写呀?谢谢大家!!!
DROP TEMPORARY TABLE IF EXISTS t;
CREATE TEMPORARY TABLE t(value varchar(500));
INSERT INTO t VALUES
('fashion scarf'),
('mens plaid scarf'),
('cashmere scarf for men'),
('mens grey scarves'),
('mens grey scarf');
-- 合并关键字
SELECT @x:=JSON_MERGE(@x, CONCAT('{"', REPLACE(value, ' ', '":1, "'), '":1}') )
FROM t, (SELECT @x:='{}') x;-- 所有不重复的关键字(需要格式的话,做一下字符串替换)
SELECT JSON_KEYS(@x);
from
(select GROUP_CONCAT(REPLACE(value,' ',',')) value from t ) a
join
mysql.help_topic b
on b.help_topic_id < (length(a.value) - length(replace(a.value,',',''))+1);
GROUP_CONCAT 的话,就更不好保证了