How to query comma separated string?

2018-06-11 12:09:47

Table_a

column name: list_id
record 1: 1,2,5,6,8
record 2: 1,3,2
record 3: 6,7,2
record 4: 9,8,0

Table_b

id ='2';

How to select records that have id='2' in the comma separated string? From the above example it should return record 1,2 and 3.

Query(How to amend this query, please?):

SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id;

使用find_in_set函数，但它不是最优化的方式，你应该规范你的数据。

SELECT * FROM 
Table_a AS a 
JOIN Table_b AS b ON FIND_IN_SET(b.id,a.list_id)

这应该适合你

SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id and 
concat(',',Table_a.list_id,',') like '%,2,%'

链接地址: http://www.djcxy.com/p/32996.html