//-- 修正一下 ,这回应该符合结果了
select * from (select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T2 ,
(select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T3
where t2.rownum = t3.rownum and t2.f1<>t3.f1 and t2.f1 > t3.f1
insert into t1 values(1,'a')
insert into t1 values(2,'a')
insert into t1 values(3,'a')
insert into t1 values(4,'a')
insert into t1 values(6,'b')
insert into t1 values(7,'b')
insert into t1 values(8,'b')
select * from (select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T2 ,
(select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T3
where t2.rownum = t3.rownum and t2.f1<>t3.f1
select V=1,BN='a' into # union all
select V=2,BN='a' union all
select V=3,BN='a' union all
select V=7,BN='b' union all
select V=8,BN='b' union all
select V=9,BN='b'
--------------------------------------
--测试数据
select V=1,BN='a' into # union all
select V=2,BN='a' union all
select V=3,BN='a' union all
select V=7,BN='b' union all
select V=8,BN='b' union all
select V=9,BN='b'
go
--查询
select a=min(case BN when 'a' then V end),
b=min(case BN when 'b' then V end)
from(select *,gid=(select count(*) from # where BN=a.BN and V<=a.V) from # a)a
group by gid
go