update tb
set xuhao = (select count(*) from tb where ownner = t.ownner and type = t.type and [length] > t.[length]) + 1
from tb t
set xuhao = (select count(*) from tb where ownner = t.ownner and type = t.type and [length] > t.[length]) + 1
from tb t
insert into tb values(1 ,'tom' ,'name1', 'A' ,333 , null )
insert into tb values(2 ,'tom' ,'name2', 'A' ,321 , null )
insert into tb values(3 ,'jake' ,'name3', 'A' ,222 , null )
insert into tb values(4 ,'jake' ,'name1', 'B' ,222 , null )
insert into tb values(5 ,'locker' ,'name2', 'B' ,111 , null )
insert into tb values(6 ,'tom' ,'name3', 'B' ,111 , null )
insert into tb values(7 ,'tom' ,'name1', 'B' ,123 , null )
insert into tb values(8 ,'jake' ,'name2', 'A' ,443 , null )
insert into tb values(9 ,'jake' ,'name3', 'A' ,234 , null )
insert into tb values(10,'locker' ,'name1', 'A' ,533 , null )
insert into tb values(11,'locker' ,'name2', 'B' ,222 , null )
insert into tb values(12,'locker' ,'name3', 'B' ,223 , null )
insert into tb values(13,'tom' ,'name1', 'B' ,44 , null )
insert into tb values(14,'tom' ,'name2', 'B' ,33 , null )
insert into tb values(15,'jake' ,'name3', 'A' ,55 , null )
insert into tb values(16,'jake' ,'name1', 'B' ,444 , null )
goupdate tb
set xuhao = (select count(*) from tb where ownner = t.ownner and type = t.type and [length] > t.[length]) + 1
from tb tselect * from tbdrop table tb/*
id ownner name type length xuhao
----------- ---------- ---------- ---------- ----------- -----------
1 tom name1 A 333 1
2 tom name2 A 321 2
3 jake name3 A 222 3
4 jake name1 B 222 2
5 locker name2 B 111 3
6 tom name3 B 111 2
7 tom name1 B 123 1
8 jake name2 A 443 1
9 jake name3 A 234 2
10 locker name1 A 533 1
11 locker name2 B 222 2
12 locker name3 B 223 1
13 tom name1 B 44 3
14 tom name2 B 33 4
15 jake name3 A 55 4
16 jake name1 B 444 1(所影响的行数为 16 行)*/