mysql逗号分隔多对多_MySQL：判断逗号分隔的字符串中是否包含某个字符串 && 如何在一个以逗号分隔的列表中的一个字段中连接MySQL中的多对多关系中的数据...

需求：

sql语句中，判断以逗号分隔的字符串中是否包含某个特定字符串，类似于判断一个数组中是否包含某一个元素，

例如：判断 ’a,b,c,d,e,f,g‘ 中是否包含 'a'，sql语句如何实现？

解决

1、mysql 字符串函数判断：   FIND_IN_SET(ele, str)

ele 是一个特定字符，

str 是一个以逗号分隔的字符串 或者 匹配这个参数的字段，必须以逗号分隔

select * from t_user where FIND_IN_SET('26', '1,26');

//ids： 以逗号分割的字符串

select * from t_user where FIND_IN_SET('26', ids);

FIND_IN_SET() 函数返回该字符串所在的位置，如果不存在就返回0,

2、ORDER BY FIND_IN_SET() 可以用来排序。

如果想让 id 按 30 27 29 排序

select * from t_user where id in(30,27,29) order by FIND_IN_SET(id,'30,29,27');

二 原文

I have a many-to-many relationship between People and Departments since one person can be in many departments.

People Departments

------ -----------

pID pName deptID deptName

1 James 1 Engineering

2 Mary 2 Research

3 Paul 3 Marketing

4 Communications

People_Departments

------------------

pID deptID

1 1

1 2

2 2

2 4

3 1

3 2

3 3

What I want is this:

pName deptName

James Engineering, Research

Mary Research, Communication

Paul Engineering, Research, Marketing

If I do plain LEFT JOINs on the tables using the SQL below, I will get several rows related to one person:

SELECT people.pName,

departments.deptName

FROM people

LEFT JOIN people_departments ON people.pID=people_departments.pID

LEFT JOIN departments ON people_departments.deptID=departments.deptID

I have tried various combinations of GROUP_CONCAT but without luck.

Any ideas to share?

解决

SELECT people.pName,

GROUP_CONCAT(departments.deptName SEPARATOR ', ') deptName

FROM people

LEFT JOIN people_departments

ON people.pID = people_departments.pID

INNER JOIN departments

ON people_departments.deptID = departments.deptID

GROUP BY people.pID

Output:

+-------+----------------------------------+

| pName | deptName |

+-------+----------------------------------+

| James | Engineering, Research |

| Mary | Research, Communications |

| Paul | Engineering, Research, Marketing |

+-------+----------------------------------+

3 rows in set (0.00 sec)

sqlserver中的解决方法

SELECT* fromcustwhere(buttonids like'%,4' or buttonids like '4,%' or buttonids like '%,4,%' or buttonids = '4')

or

(buttonids like'%,5' or buttonids like '5,%' or buttonids like '%,5,%' or buttonids = '5')

(转载https://blog.csdn.net/qq_42402854/article/details/88030469  &&  https://www.it1352.com/1475016.html)