? 每个q_id下出现次数前2位的remark(只取两位)
不知道是否能够用一条sql解决
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解决