XX在前几位的数据

？ 每个q_id下出现次数前2位的remark(只取两位)

create table Test(
	q_id int,
	remark varchar(20)
)

--drop table Test

insert into Test(q_id,remark) values(1,'1');
insert into Test(q_id,remark) values(2,'1');
insert into Test(q_id,remark) values(3,'2');
insert into Test(q_id,remark) values(4,'3');
insert into Test(q_id,remark) values(5,'2');
insert into Test(q_id,remark) values(6,'1');
insert into Test(q_id,remark) values(7,'1');

insert into Test(q_id,remark) values(1,'2');
insert into Test(q_id,remark) values(2,'1');
insert into Test(q_id,remark) values(3,'2');
insert into Test(q_id,remark) values(4,'3');
insert into Test(q_id,remark) values(5,'1');
insert into Test(q_id,remark) values(6,'2');
insert into Test(q_id,remark) values(7,'3');

insert into Test(q_id,remark) values(1,'3');
insert into Test(q_id,remark) values(2,'2');
insert into Test(q_id,remark) values(3,'2');
insert into Test(q_id,remark) values(4,'3');
insert into Test(q_id,remark) values(5,'2');
insert into Test(q_id,remark) values(6,'3');
insert into Test(q_id,remark) values(7,'1');



select t.q_id,t.remark,count(t.remark) as counts
,(select top 1 q_id from Test where q_id=t.q_id)
from Test t
group by t.q_id,t.remark
order by t.q_id 

create proc proc_test
as
begin
	declare cur cursor scroll dynamic
	for
	select distinct q_id from Test
	declare @qId int
	open cur
	fetch next from cur into @qId
	while(@@fetch_status=0)
	begin
		select top 2 a.*
		from 
		(select q_id,remark,count(remark) as c 
			from test where q_id=@qId group by q_id,remark) a
		order by a.c desc
		fetch next from cur into @qId
	end
	close cur
	deallocate cur
end
go

exec proc_test

--drop proc proc_test

不知道是否能够用一条sql解决