问题描述
我有一个mysql表
id cid c_name关键字
1 28斯图加特BW,梅赛德斯,保时捷,汽车,德国
2 34罗姆塞萨尔,AS罗马
3 28慕尼黑宝马节,慕尼黑啤酒节,汽车,德国
我需要查询显示来自cid = 28的关键字,但是我只想看到1次关键字,例如(BW,Mercedes,Porsche,Auto,Bmw,Oktober Fest,德国)
i不想列出2次关键字,如何解决这个问题吗?
我尝试了不同但无法获得我想要的东西
在将其全部添加到DISTINCT上之前将其拆分。 > 选择
GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,',',n.digit + 1),',',-1))关键字
FROM
t
内部联接
(选择0位UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6)n
LENGTH(REPLACE(keywords) ,',',''))< =长度(关键字)-n.digit
WIDE cid = 28
i have a mysql table
id cid c_name keywords
1 28 Stutgart BW,Mercedes,Porsche,Auto,Germany
2 34 Roma Sezar,A.S. Roma
3 28 München BMW,Oktober Fest,Auto,Germany
i need a query to show keywords from cid=28 but i want to see only 1 time a keyword, like (BW,Mercedes,Porsche,Auto,Bmw,Oktober Fest,Germany)i dont want to list 2 time a keyword, how can resolve this problem?
i have tried distinct but could not get what i want
Split it before adding it all up with DISTINCT.Of course,better is to normalize your data(no more than 1 value in a column)
SELECT
GROUP_CONCAT( DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(keywords, ',', n.digit+1), ',', -1)) keyword
FROM
t
INNER JOIN
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
ON LENGTH(REPLACE(keywords, ',' , '')) <= LENGTH(keywords)-n.digit
WHERE cid=28
这篇关于MySQL选择不同的逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!