用临时表进行处理,加个自增唯一列,然后再进行统计: select id=identity(int,1,1),* into #aa from yourtable select * from #aa a where a.id in(select top 2 id from #aa where gsid=a.gsid) and a.gsid<>'004'
select id = identity(int, 1, 1),* into #tTemp from t1 select gsid, name from #tTemp a where a.gsid<>'004' and a.id in(select top 2 id from #tTemp where gsid=a.gsid)
select id=identity(int,1,1),* into #aa from yourtable
select * from #aa a where a.id in(select top 2 id from #aa where gsid=a.gsid)
and a.gsid<>'004'
表如下:
字段如下
gsid(pk) a b
0001 asdas asdasdasd
0001 jkjkj asdasdasd
0001 vbvbvb asdasdasd
0001 weweas asdasdasd
0002 xcvxcv cbcvbcvvcb
0002 xcvxcv cbcvbcvvcb
0002 xcvxcv cbcvbcvvcb
0003 gfhfgh hjkhjk
0004 dfdfdf 98898
要求找出非'0004'的数据且每种数据最多两条记录,(这里'每种'指gsid字段决定的值如0001,0002,0003)而且不能包括'0004'
select gsid, name from #tTemp a where a.gsid<>'004' and a.id in(select top 2 id from #tTemp where gsid=a.gsid)