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.
如何在逗号分隔的字符串中选择id ='2'的记录?从上面的例子中,它应该返回记录1,2和3。
Query(How to amend this query, please?):
查询(如何修改此查询?):
SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id;
2 个解决方案
#1
3
use find_in_set function, but it is not optimized way you should normalize your data.
使用find_in_set函数,但它没有优化的方式来规范化您的数据。
SELECT * FROM
Table_a AS a
JOIN Table_b AS b ON FIND_IN_SET(b.id,a.list_id)
#2
0
This should work for you
这应该适合你
SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id and
concat(',',Table_a.list_id,',') like '%,2,%'
#1
3
use find_in_set function, but it is not optimized way you should normalize your data.
使用find_in_set函数,但它没有优化的方式来规范化您的数据。
SELECT * FROM
Table_a AS a
JOIN Table_b AS b ON FIND_IN_SET(b.id,a.list_id)
#2
0
This should work for you
这应该适合你
SELECT * FROM Table_a,Table_b WHERE Table_b.id = Table_a.list_id and
concat(',',Table_a.list_id,',') like '%,2,%'