mysql 大字段取值问题

[hehe4569]

有如下表记录
mysql> select * from test;
+------+-----------------------+--------+
| id | link | status |
+------+-----------------------+--------+
| 1 | 31,20,40 | 1 |
| 2 | 31,20,40,34,32,91,109 | 0 |
| 2 | 31,20,40,34,32 | 1 |
| 4 | 31,20,40,34,32,49 | 0 |
+------+-----------------------+--------+
我想取到状态为0的，link值记录40以后的那些记录值，就是要取到32，34，49，91，109
该如何取到？SQL怎么实现？

[ACMAIN_CHM]

(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[loveflea]

select * from test where status=0 (FIND_IN_SET('32',link) or FIND_IN_SET('34',link) or FIND_IN_SET('49',link) or FIND_IN_SET('91',link) or FIND_IN_SET('109',link));

mysql> help find_in_set;
Name: 'FIND_IN_SET'
Description:
Syntax:
FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the
string list strlist consisting of N substrings. A string list is a
string composed of substrings separated by "," characters. If the first
argument is a constant string and the second is a column of type SET,
the FIND_IN_SET() function is optimized to use bit arithmetic. Returns
0 if str is not in strlist or if strlist is the empty string. Returns
NULL if either argument is NULL. This function does not work properly
if the first argument contains a comma (",") character.

URL: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Examples:
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2

[loveflea]

select * from test where status=0 and (FIND_IN_SET('32',link) or FIND_IN_SET('34',link) or FIND_IN_SET('49',link) or FIND_IN_SET('91',link) or FIND_IN_SET('109',link));

[WWWWA]

比较复杂：
SELECT GROUP_CONCAT(bb1.ss2 SEPARATOR ',') FROM (
SELECT CONCAT(',',a.link,',') AS aa,
REPLACE(MID(CONCAT(',',a.link,','),b.id,INSTR(MID(CONCAT(a.link,','),b.id,LENGTH(CONCAT(a.link,','))),',')+1),',','')
AS ss2,b.id

FROM tty2 a LEFT JOIN lsb1 b ON LENGTH(a.link)+2>=b.id
WHERE a.status=0 AND MID(CONCAT(',',a.link,','),b.id,1)=','

ORDER BY aa) bb
LEFT JOIN

(SELECT CONCAT(',',a.link,',') AS aa,
REPLACE(MID(CONCAT(',',a.link,','),b.id,INSTR(MID(CONCAT(a.link,','),b.id,LENGTH(CONCAT(a.link,','))),',')+1),',','')
AS ss2,b.id

FROM tty2 a LEFT JOIN lsb1 b ON LENGTH(a.link)+2>=b.id
WHERE a.status=0 AND MID(CONCAT(',',a.link,','),b.id,1)=','

ORDER BY aa) bb1
ON bb1.id>bb.id AND bb1.aa=bb.aa

WHERE bb.ss2='40'

LSB1：字段ID，内容1-10000
你可以将
SELECT CONCAT(',',a.link,',') AS aa,
REPLACE(MID(CONCAT(',',a.link,','),b.id,INSTR(MID(CONCAT(a.link,','),b.id,LENGTH(CONCAT(a.link,','))),',')+1),',','')
AS ss2,b.id

FROM tty2 a LEFT JOIN lsb1 b ON LENGTH(a.link)+2>=b.id
WHERE a.status=0 AND MID(CONCAT(',',a.link,','),b.id,1)=','

ORDER BY aa
生成VIEW