select operator,operation
,(select count(*) from lotDetail where operator=a.operator and operation<=a.operation) as id
from lotDetail a order by operator,operation
,(select count(*) from lotDetail where operator=a.operator and operation<=a.operation) as id
from lotDetail a order by operator,operation
(select count(*) from lotDetail
where operator=A.operator and operation<=A.operation) id
from lotDetail A order by operator,operation]
测试数据
create Table #Table1 (operator CHAR(3),operation INT)insert into #Table1
select '001' ,10
union select '001',11
union select '001',9
union select '002',9
union select '002' ,11
union select '002',10
union select '002',8
select operator,operation,
(select count(*) from #Table1 where operator=A.operator and operation<=A.operation) id
from #Table1 A order by operator,operationdrop table #Table1