mysql in select 子句_mysql where in子句与in子句后的select语句

I have a varchar field which store values like 2,1 and I am using a query to select the data. The query is this one:

SELECT no

FROM c_head

where id = 9

which give output 2,1

and after that, I use this query

SELECT id, item

FROM c_item

where type_head IN (SELECT no

FROM c_head

where id = 9)

ORDER BY item

and its not working its working like type_head IN (2) instead of type_head IN (2,1)

but when I simply use hard coded its working

SELECT id, item

FROM c_item

where type_head IN (2,1)

ORDER BY item

but its not working with subquery why?

please help me

解决方案

To store comma separated values in column is bad design you should look at Database Normalization and do normalize your structure by storing all related type_head in a junction table,but if you can't change structure so in mysql you can use FIND_IN_SET() and join your c_head table,it will select records if it find the value in provided set,IN() will not work for values in set or in comma separated list

SELECT i.id, i.item

FROM c_item i

JOIN c_head h ON(FIND_IN_SET(i.type_head,h.no) > 0)

WHERE h.id = 9

ORDER BY i.item