有如下表记录
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怎么实现?
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怎么实现?
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
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.htmlExamples:
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
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.aaWHERE 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