有这样一个表tbl1(ID, Idx, ColA, ColB, MCaID)ID Idx A MCaID
-------------------------------------------
1 NULL XXXXXXXX 1
2 NULL asdf 1
3 NULL asdf 1
4 NULL sdf 1
5 NULL fasdf 2
6 NULL asdf 2
7 NULL dfas 2
8 NULL fasdf 2
9 NULL fsdas 3
10 NULL fasdf 3
11 NULL asdf 3现在我想根据MCaID在Idx列中填入顺序号,如:
ID Idx A MCaID
-------------------------------------------
1 1 XXXXXXXX 1
2 2 asdf 1
3 3 asdf 1
4 1 fasdf 2
5 2 asdf 2
6 3 dfas 2
7 4 fasdf 2
8 1 fsdas 3
9 2 fasdf 3
10 3 asdf 3
-------------------------------------------
1 NULL XXXXXXXX 1
2 NULL asdf 1
3 NULL asdf 1
4 NULL sdf 1
5 NULL fasdf 2
6 NULL asdf 2
7 NULL dfas 2
8 NULL fasdf 2
9 NULL fsdas 3
10 NULL fasdf 3
11 NULL asdf 3现在我想根据MCaID在Idx列中填入顺序号,如:
ID Idx A MCaID
-------------------------------------------
1 1 XXXXXXXX 1
2 2 asdf 1
3 3 asdf 1
4 1 fasdf 2
5 2 asdf 2
6 3 dfas 2
7 4 fasdf 2
8 1 fsdas 3
9 2 fasdf 3
10 3 asdf 3
from tbl1 a
from tbl1 as A
insert test select 1,null,'XXXXXXXX',1
union all select 2,null,'asdf',1
union all select 3,null,'sdf',1
union all select 4,null,'asdf',1
union all select 5,null,'asdf',2
union all select 6,null,'dfas',2
union all select 7,null,'asdf',2
union all select 8,null,'fasdf',2select ID,Idx=(select count(*)+1 from test where MCaID=a.MCaID and ID<a.ID),A,MCaID
from test adrop table test
from tab1 a