关于identity的题目

题目描述

字段A 字段B 字段C
101 AA AA
101 BB BB
101 CC CC
102 DD DD
102 EE EE
102 FF FF
103 GG GG
103 HH HH
103 II II
........

在这个表中存在着很多“字段A”相同的列，我想根据“字段A”把相同"字段A"的数据的前2条查询出来
想要的结果是
字段A 字段B 字段C
101 AA AA
101 BB BB
102 DD DD
102 EE EE
103 GG GG
103 HH HH

解答方法

create table [tb]([A] int,[B] varchar(2),[C] varchar(2))
insert [tb]
select 101,'AA','AA' union all
select 101,'BB','BB' union all
select 101,'CC','CC' union all
select 102,'DD','DD' union all
select 102,'EE','EE' union all
select 102,'FF','FF' union all
select 103,'GG','GG' union all
select 103,'HH','HH' union all
select 103,'II','II'

--标记identity后将信息存入另外一个表#

select *,px=identity(int,1,1) into # from tb
go

--嵌套查询
select a,b,c
from # t
where (select count(*) from # b where a=t.a and px<=t.px)<=2
go