MYSQL从字段所在的表中选择

UPDATED

更新

id  |  id_list
1   |  2,3,5,7
2   |  1,4,5,6
3   |  1,4,6,7
4   |  2,3,5,8
5   |  1,2,4,8
6   |  2,3,7,8
7   |  1,3,6,9
8   |  4,5,6,9
9   |  7,8

let's say I'm up to the content of id=1

假设我的id等于1

I wanted to select all the rows where id is in id_list of id=1 PLUS the row where id=1

我想选择id在id_list中的所有行，id=1加上id=1的行

so the result would be rows with id = 1,2,3,5,7

结果是id为1 2 3 5 7的行

How to do this query guys?

如何做这个查询呢?

7 个解决方案

#1

You can also use a self join

您还可以使用自连接

Using IN()

使用在()

select * from atable a
join atable b on (a.id = b.id )
where 1 IN (a.id_list) or   b.id =1

Fiddle with IN()

小提琴在()

Using FIND_IN_SET()

使用FIND_IN_SET()

select * from atable a
join atable b on (a.id = b.id )
where FIND_IN_SET('1', a.id_list) or   b.id =1

Fiddle with FIND_IN_SET()

摆弄FIND_IN_SET()

Using UNION ALL

使用UNION ALL

select * from atable 
where id =1
UNION ALL 
select * from atable 
where 1 IN (id_list)

Fiddle with UNION ALL

摆弄UNION ALL

#2

Your database design is broken; id_list should be represented as a join table instead of as a string. However, to solve your immediate problem:

您的数据库设计被破坏了;id_list应该表示为连接表，而不是字符串。然而，为了解决你眼前的问题:

select * from table where id=1 
          or id_list like '1%' 
          or id_list like '%,1,%'
          or id_list like '%,1'

Adjust as needed for PreparedStatement. You have to provide all three cases because if you just did

根据需要调整准备语句。你必须提供这三种情况因为如果你做了

  or id_list like '%1%'

and id_list contained the value 11, you'd get an incorrect match

id_list包含值11，会得到不正确的匹配

#3

Try this (see SQL-Fiddle):

试试这个(见SQL-Fiddle):

SELECT * FROM tbl 
WHERE id = 1
OR INSTR((SELECT id_list FROM tbl WHERE id = '1'), id) > 0;

Tested with MySQL 5.5.30

测试与MySQL 5.5.30

#4

try this one

试试这个

select * 
from tbl 
where id=1 
or id_list like '%1%'

#5

This appears to call for a union of two sets. The one set would be the single row whose id matches the specified value:

这似乎需要两个集合的联合。一组是id匹配指定值的单行:

SELECT
  id
FROM
  atable
WHERE
  id = @id

The other set would be the result of this self-join:

另一组则是自加入的结果:

SELECT
  item.id
FROM
  atable AS item
INNER JOIN
  atable AS list
ON
  FIND_IN_SET(item.id, list.id_list)
WHERE
  list.id = @id

That is, the row with the specified id is matched against every row in the table on the condition that the other row's id is found in the specified row's id_list.

也就是说，具有指定id的行与表中的每一行匹配，条件是在指定行的id_list中找到另一行的id。

You can try the complete query at SQL Fiddle.

您可以在SQL Fiddle尝试完整的查询。

Please note that lists aren't a very good design feature. In your situation, it might be better to use a many-to-many table as suggested by @Othman. Only I would probably use a slightly different query to get the desired output, because his doesn't include the specified row itself:

请注意列表并不是一个很好的设计特性。在您的情况下，最好使用@Othman建议的多对多表。只有我可能会使用稍微不同的查询来获得所需的输出，因为his不包含指定的行本身:

SELECT
  id
FROM
  manytomany
WHERE
  id = @id

UNION

SELECT
  linked_id
FROM
  manytomany
WHERE
  id = @id
;

While the entries in manytomany are assumed to be unique, the query uses the UNION DISTINCT operator because of the potential duplicates returned by the first subquery, although it is possible to move the application of DISTINCT to the first subquery only like this:

虽然许多tomany中的条目被假定为唯一的，但是查询使用UNION DISTINCT操作符，因为第一个子查询返回了潜在的重复，尽管可以将DISTINCT的应用程序移动到第一个子查询，如下所示:

SELECT DISTINCT
  id
FROM
  manytomany
WHERE
  id = @id

UNION ALL

SELECT
  linked_id
FROM
  manytomany
WHERE
  id = @id
;

That first subquery could actually be rewritten simply as SELECT @id AS id, but the rewrite would only make sense if the passed value was guaranteed to be valid, i.e. that it would definitely be found in manytomany.id.

第一个子查询实际上可以简单地重写为SELECT @id作为id，但是重写只有在传递的值被保证为有效的情况下才有意义，即它一定会在manytomany.id中找到。

Here's a demo for the other approach too (all three variations, including the SELECT @id AS id one).

这里还有另一种方法的演示(所有三种变体，包括SELECT @id作为id 1)。

#6

-1

I don't have an answer for your question but I encourage you to redesign your table like this I think this called many to many relation

我对你的问题没有答案，但是我鼓励你重新设计你的表格，像这样，我认为这涉及到很多很多的关系

id  |  friend
1   |    2
1   |    3
1   |    5
1   |    7
2   |    1
2   |    4
2   |    5
2   |    6
3   |    1
3   |    4
3   |    6
3   |    7

And then your query will be like this

然后你的查询会是这样的

SELECT DISTINCT(friend) FROM `new` WHERE id = 1

#7

-1

I am assuming you are using php..
My suggestion is to grab the id_list for id 1.
Explode that id_list on the comma, and then do another mysql query to grab the remaining results for 5|3|6|8
ex) $idarray = explode(",", $result);
select * from your_table where id in ('5','3','6','8')

OPTION 2:

我想你是在用php。我的建议是获取id 1的id_list。在逗号上展开id_list，然后执行另一个mysql查询，获取5|3|6|8 ex的剩余结果)$idarray = explosion(“，”，“结果”);从your_table中选择*，其中id为('5'，'3'，'6'，'8')选项2:


SELECT * FROM your_table
WHERE id = '1'
OR id IN ('\''+(SELECT REPLACE(id_list,',','\',\'') FROM your_table WHERE id = '1')+'\'')

EDIT: Oops, sorry, that should be an OR instead.

编辑:哦，对不起，应该是OR。

#1