取每组中前2行.txt

来自「SQL语言常用的一些命令各代码」· 文本 代码 · 共 31 行

/*************题目

item1 item2a      1

a      2

a      3

b      1

b      2

b      3

c      1

c      2c      3
结果

item1 item2

a      1

a      2

b      1

b      2

c      1

c      2

**************/

/*建表*/

create table #item(item1 char(1),item2 int)

insert into #item select 'a',      1union all          select 'a',      2

union all          select 'a',      3

union all          select 'b',      1

union all          select 'b',      2

union all          select 'b',      3

union all          select 'c',      1

union all          select 'c',      2

union all          select 'c',      3 /*解法1*/

select * from #item a 

where checksum(*) in (select top 2 checksum(*) from #item b where a.item1 = b.item1 order by item2) /*解法2*/

select * from #item  a where (select count(item2) from #item b where b.item1=a.item1 and b.item2<=a.item2)<=2