table1ID index
1 1
5 5
7 3
8 5
9 9
10 1
11 10
. .
. .
. .将表更新为:
ID index
1 1
5 2
7 3
8 4
9 5
10 6
11 7
. .
. .
. .就是将: select * from Table1 ORDER BY ID ,排序后的序号写入相应index
求SQL语句
1 1
5 5
7 3
8 5
9 9
10 1
11 10
. .
. .
. .将表更新为:
ID index
1 1
5 2
7 3
8 4
9 5
10 6
11 7
. .
. .
. .就是将: select * from Table1 ORDER BY ID ,排序后的序号写入相应index
求SQL语句
SET @index = 0DECLARE CUR CURSOR FOR
SELECT ID FROM TABLE1
ORDER BY IDOPEN CUR
FETCH NEXT FROM CUR INTO @IDWHILE @@FETCH_STATUS = 0
BEGIN
SET @index = @index + 1
UPDATE TABLE1 SET [index]=@index WHERE ID=@ID
FETCH NEXT FROM CUR INTO @ID
ENDCLOSE CUR
DEALLOCATE CUR但是数据量大的时候,不建议用SQL语句完成这个操作,因为Cursor的速度比较慢。
表TempTalbe
index(自动编号)
1
2
3
4
5
6
7
.
.
.
for i=0 to 20
set rs=server.createobject("adodb.recordset")
sql="update t1 set index="&i&" where id="&id
rs.open sql,conn,1,1
i=i+1
DataTable datatable1 = 根据SQL语句取得的数据集
for(int i=0;i<datatable1.Rows.Count;i++)
{
sql = "update table set index = " + (i+1) + " where id=" + datatable1.Rows[i][0].ToString() + "";
//执行SQL语句
}
for(int i=0;i<dataset11.tables(0).Rows.Count;i++)
{
sql = "update table set index = " + (i+1) + " where id=" + datatable1.Rows[i][0].ToString() + "";