mysql将数据传递给in
基本上我想从 tblvw1 获取一些数据.另一个表包含列中可能的 ID，该列存储为连接字符串，例如:1|2|3|4".接下来我尝试通过以下查询获得结果:
Basically I want to fetch some data from tblvw1. Another table contains possible ids within a column which is stored as concatenated string, for example: "1|2|3|4". Next I have tried to get the result by the following query:
SELECT x FROM tblname1
WHERE id IN (SELECT REPLACE(content,'|',',') FROM tblname2 WHERE dataid = y )
作为结果，我只得到第一个值，没有获取其他数据.我想子查询会产生这种形式:
AS result I only get the first value, the other data wasn't fetched. I suppose that the subquery will result in this form:
SELECT x FROM tblname1 WHERE id IN ('1,2,3,4')
但我想将这个子查询作为
but i want to have this subquery either as
SELECT x FROM tblname1 WHERE id IN (1,2,3,4)
或
SELECT x FROM tblname1 WHERE id IN ('1','2','3','4')
有什么想法吗?
推荐答案
可以使用find_in_set函数吗?
SELECT x FROM tblname1 t1
inner join tblname2 t2 on find_in_set (t1.id, REPLACE(t2.content,'|',',')) > 0
where t2.dataid = 'y';
find_in_set 函数返回第一个参数在第二个参数中的位置.如果结果>0，则已找到第一个参数.
The find_in_set function returns the position of the first argument within the second argument. If the result is >0, then the first argument has been found.
参见 http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set
相关文章