update T set [order]=(select count(1) from A where Field1=T.Field1 and Field2<=T.Field2) from A T where Field1=1
declare @tb table ( Field1 int, Field2 int, [Order] int ) insert @tb select 1, 11, 0 union all select 1, 5 , 0 union all select 3, 2 , 0 union all select 1, 4 , 0 union all select 1, 15, 0 union all select 4, 2 , 0 union all select 1, 7 , 0 union all select 2, 2 , 0 union all select 3, 2 , 0 union all select 1, 2 , 0--更新 update t set [Order]=(select count(1) from @tb where Field1=t.Field1 and Field2<=t.Field2) from @tb t where Field1=1--查看 select * from @tb where Field1=1--结果 /* Field1 Field2 Order ----------- ----------- ----------- 1 11 5 1 5 3 1 4 2 1 15 6 1 7 4 1 2 1(所影响的行数为 6 行) */
update t set Order = (select count(*) from A where Field1=t.Field1 and Field2>=t.Field2) from A t where t.Field1 = 1
偶在上面写成降序排列了:update t set Order = (select count(*) from A where Field1=t.Field1 and Field2<=t.Field2) from A t where t.Field1 = 1
update T set [order]=(select count(*) from A where Field1=T.Field1 and Field2<=T.Field2) from A T where Field1=1
set [order]=(select count(1) from A where Field1=T.Field1 and Field2<=T.Field2)
from A T
where Field1=1
(
Field1 int, Field2 int, [Order] int
)
insert @tb
select 1, 11, 0 union all
select 1, 5 , 0 union all
select 3, 2 , 0 union all
select 1, 4 , 0 union all
select 1, 15, 0 union all
select 4, 2 , 0 union all
select 1, 7 , 0 union all
select 2, 2 , 0 union all
select 3, 2 , 0 union all
select 1, 2 , 0--更新
update t
set [Order]=(select count(1)
from @tb
where Field1=t.Field1
and Field2<=t.Field2)
from @tb t
where Field1=1--查看
select * from @tb where Field1=1--结果
/*
Field1 Field2 Order
----------- ----------- -----------
1 11 5
1 5 3
1 4 2
1 15 6
1 7 4
1 2 1(所影响的行数为 6 行)
*/
set
Order = (select count(*) from A where Field1=t.Field1 and Field2>=t.Field2)
from
A t
where
t.Field1 = 1
set
Order = (select count(*) from A where Field1=t.Field1 and Field2<=t.Field2)
from
A t
where
t.Field1 = 1
set [order]=(select count(*) from A where Field1=T.Field1 and Field2<=T.Field2)
from A T
where Field1=1