select idd=identity(int,1,1),cast(id as int) as id,result
into #t
from tb1
order by resultselect *
from #t a
where (select count(*) from #t where (id-idd)=(a.id-a.idd) and result=a.result)=3
into #t
from tb1
order by resultselect *
from #t a
where (select count(*) from #t where (id-idd)=(a.id-a.idd) and result=a.result)=3
------------------
drop table #t
select idd=identity(int,1,1),cast(id as int) as id,result
into #t
from tb1
order by resultselect *
from #t a
where (select count(*) from #t where (id-idd)=(a.id-a.idd) and result=a.result)=3---------------------------------------------------(所影响的行数为 20 行)idd id result
----------- ----------- -----------
1 1 10
2 2 10
3 3 10
6 11 10
7 12 10
8 13 10(所影响的行数为 6 行)------------------------------------------
但没有ID为8,9,10的记录啊.
select idd=identity(int,1,1),cast(id as int) as id,result
into #t
from tb1
order by result,idselect *
from #t a
where (select count(*) from #t where (id-idd)=(a.id-a.idd) and result=a.result)=3
--select * from #t
-----------------------------(所影响的行数为 20 行)idd id result
----------- ----------- -----------
1 1 10
2 2 10
3 3 10
6 11 10
7 12 10
8 13 10
15 8 20
16 9 20
17 10 20(所影响的行数为 9 行)
into #t
from tb1
order by result,id第一条加上这个
select idd=identity(int,1,1),cast(id as int) as id,result
into #t
from tb1
order by result,idselect *
from #t a
where (select count(*) from #t where (id-idd)=(a.id-a.idd) and result=a.result)=3
--select * from #t
--子查询为外查询的附加条件(where).
--result=a.result考虑到#T最后一条记录已经符合(id-idd)=(a.id-a.idd)