mysql> select instr(10001100001110000,1); +----------------------------+ | instr(10001100001110000,1) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec)mysql> select instr(10001100001110000,11); +-----------------------------+ | instr(10001100001110000,11) | +-----------------------------+ | 5 | +-----------------------------+ 1 row in set (0.02 sec)mysql> select instr(10001100001110000,111); +------------------------------+ | instr(10001100001110000,111) | +------------------------------+ | 11 | +------------------------------+ 1 row in set (0.01 sec)楼主要的结果是这样?
SELECT MIN(id) AS 位置,MAX(id)-MIN(id)+1 AS 长度,REPEAT('1',MAX(id)-MIN(id)+1) AS newcl FROM ( SELECT *,SUBSTR(ss,b1.id,1) AS qq, ( SELECT MAX(id) FROM ( SELECT *,SUBSTR(ss,b.id,1) AS ss2 FROM (SELECT '100100000101011111010101' AS ss ) a LEFT JOIN zz.lsb1 b ON LENGTH(ss)>=b.id) a2 WHERE b1.id>a2.id AND ss2='0') dd FROM (SELECT '100100000101011111010101' AS ss ) a1 LEFT JOIN zz.lsb1 b1 ON LENGTH(ss)>=b1.idWHERE SUBSTR(ss,b1.id,1)='1') qq GROUP BY dd建立LSB1,字段ID,内容1-100000
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
意思有365个0或者1的数字组成的串,
0代表上班 1代表旷工需要计算每次旷工的时间
多行
比如: 10001100001110000
return:
1 位置:1 长度:1
11 位置:5 长度:2
111 位置:11 长度:3
+----------------------------+
| instr(10001100001110000,1) |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)mysql> select instr(10001100001110000,11);
+-----------------------------+
| instr(10001100001110000,11) |
+-----------------------------+
| 5 |
+-----------------------------+
1 row in set (0.02 sec)mysql> select instr(10001100001110000,111);
+------------------------------+
| instr(10001100001110000,111) |
+------------------------------+
| 11 |
+------------------------------+
1 row in set (0.01 sec)楼主要的结果是这样?
SELECT *,SUBSTR(ss,b1.id,1) AS qq,
(
SELECT MAX(id) FROM (
SELECT *,SUBSTR(ss,b.id,1) AS ss2 FROM
(SELECT '100100000101011111010101' AS ss ) a LEFT JOIN zz.lsb1 b ON LENGTH(ss)>=b.id) a2 WHERE b1.id>a2.id AND
ss2='0') dd
FROM
(SELECT '100100000101011111010101' AS ss ) a1 LEFT JOIN zz.lsb1 b1 ON LENGTH(ss)>=b1.idWHERE SUBSTR(ss,b1.id,1)='1') qq GROUP BY dd建立LSB1,字段ID,内容1-100000