MySQL选择不同的逗号分隔值

MySQL选择不同的逗号分隔值

本文介绍了MySQL选择不同的逗号分隔值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个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

See it working

这篇关于MySQL选择不同的逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-26 00:11