name value a 1 a 2 a 3 b 4 b 5 b 6 我要求显示为 a 1 a 2 b 4 b 7 也就是说我要显示每组的前两条记录
select identity(int,1,1) [id], [name], [value] into #t from 表 order by nameselect [name],[value] from #t A, (select min(id),[name] from #t group by [name]) B where A.id in ( B.id, B.id+1 ) and A.[name] = B.[name]drop table #t
create table tab_yourtable(name varchar(20), value int) insert into tab_yourtable select 'a', 1 union select 'a', 2 union select 'a', 3 union select 'b', 4 union select 'b', 5 union select 'b', 6select IDENTITY(int, 1, 1) as id, * into tab_tmpTable from tab_yourtableselect name, value from tab_tmpTable where id in (select min(id) from tab_tmpTable group by name union select min(id)+1 from tab_tmpTable group by name) group by name, value order by namedrop table tab_tmpTable drop table tab_yourtable
a 1
a 2
a 3
b 4
b 5
b 6
我要求显示为
a 1
a 2
b 4
b 7
也就是说我要显示每组的前两条记录
insert into tab_yourtable
select 'a', 1
union
select 'a', 2
union
select 'a', 3
union
select 'b', 4
union
select 'b', 5
union
select 'b', 6select IDENTITY(int, 1, 1) as id, * into tab_tmpTable from tab_yourtableselect name, value from tab_tmpTable
where id in (select min(id) from tab_tmpTable group by name
union
select min(id)+1 from tab_tmpTable group by name)
group by name, value
order by namedrop table tab_tmpTable
drop table tab_yourtable