表中有一个字段是NO
No state
0 0
1 0
2 0
3 0
0 0
1 0
2 0
3 0
4 0
5 0
6 0
.....
请问如何写SQL语句可以查询到第一个0~3这个范围内的数据,查完后,把这些数据状态设为1,然后再取下面的0~6,然后把这几条状态也设为1,然后再到0~n,依次类推.
在线等
No state
0 0
1 0
2 0
3 0
0 0
1 0
2 0
3 0
4 0
5 0
6 0
.....
请问如何写SQL语句可以查询到第一个0~3这个范围内的数据,查完后,把这些数据状态设为1,然后再取下面的0~6,然后把这几条状态也设为1,然后再到0~n,依次类推.
在线等
where a.state=0 and a.no=[0~3]
and a.rownumber in
(select min(rownumber) from table b where b.no=a.no )
----------------------
这样试试
SQL> SELECT * FROM TB;NO STATE
---------- ----------
0 0
1 0
2 0
3 0
0 0
1 0
2 0
3 0
4 0
5 0
6 0
0 0
1 0
0 0已选择14行。SQL> UPDATE TB SET STATE=1 WHERE
2 ROWID<(SELECT MIN(ROWID) FROM TB WHERE NO='0' AND STATE='0'
3 AND ROWID <>(SELECT MIN(ROWID) FROM TB WHERE STATE=0)) AND STATE='0';已更新4行。SQL> SELECT * FROM TB;NO STATE
---------- ----------
0 1
1 1
2 1
3 1
0 0
1 0
2 0
3 0
4 0
5 0
6 0
0 0
1 0
0 0已选择14行。SQL> UPDATE TB SET STATE=1 WHERE
2 ROWID<(SELECT MIN(ROWID) FROM TB WHERE NO='0' AND STATE='0'
3 AND ROWID <>(SELECT MIN(ROWID) FROM TB WHERE STATE=0)) AND STATE='0';已更新7行。SQL> SELECT * FROM TB;NO STATE
---------- ----------
0 1
1 1
2 1
3 1
0 1
1 1
2 1
3 1
4 1
5 1
6 1
0 0
1 0
0 0已选择14行。SQL> UPDATE TB SET STATE=1 WHERE
2 ROWID<(SELECT MIN(ROWID) FROM TB WHERE NO='0' AND STATE='0'
3 AND ROWID <>(SELECT MIN(ROWID) FROM TB WHERE STATE=0)) AND STATE='0';已更新2行。SQL> SELECT * FROM TB;NO STATE
---------- ----------
0 1
1 1
2 1
3 1
0 1
1 1
2 1
3 1
4 1
5 1
6 1
0 1
1 1
0 0已选择14行。SQL>