数据表:tableid talent
1 1,2,4,3
2 1,3
3 3,22,13,1
4 11,21,31,13,10,33
5 1,12,13现在想查询含有1和3的值的行,需要的结果为返回ID:1 2 3共三行。
1 1,2,4,3
2 1,3
3 3,22,13,1
4 11,21,31,13,10,33
5 1,12,13现在想查询含有1和3的值的行,需要的结果为返回ID:1 2 3共三行。
调试欢乐多
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/01/4141864.aspx
and instr(LPAD(RPAD(Group_id,length(Group_id)+1,','),length(Group_id)+2,','),',1,')<>0
Database changed
mysql> select * from a;
+----+----------------+
| id | Talent |
+----+----------------+
| 1 | 1,2,3,4,5 |
| 2 | 1,3 |
| 3 | 1,123,132,3,34 |
| 4 | 1,12,13 |
+----+----------------+
4 rows in set (0.02 sec)mysql> SELECT * FROM a where instr(LPAD(RPAD(talent,length(talent)+1,','),length
(talent)+2,','),',3,')<>0
-> and instr(LPAD(RPAD(talent,length(talent)+1,','),length(talent)+2,','),',
1,')<>0;
+----+----------------+
| id | Talent |
+----+----------------+
| 1 | 1,2,3,4,5 |
| 2 | 1,3 |
| 3 | 1,123,132,3,34 |
+----+----------------+
3 rows in set (0.00 sec)mysql>