如:
declare @源 table(id numeric,组 varchar(10),值 varchar(10));
insert into @源(id,组,值) values(1,1,'a')
insert into @源(id,组,值) values(2,1,'b')
insert into @源(id,组,值) values(3,2,'c')
insert into @源(id,组,值) values(4,2,'d')
insert into @源(id,组,值) values(5,2,'e')
insert into @源(id,组,值) values(6,3,'f')
select * from @源
取 各"组"中前两条记录要求:
1.单SQL解决,别用游标.游标偶会...下面是游标:
declare @组 varchar(10);
declare @rtn table(id numeric,组 varchar(10),值 varchar(10));
declare cur cursor for select distinct 组 from @源;
OPEN cur;
FETCH NEXT FROM cur INTO @组;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @rtn select top 2 id,组,值 from @源 where 组=@组
FETCH NEXT FROM cur INTO @组;
END
CLOSE cur
DEALLOCATE cur
select * from @rtn
declare @源 table(id numeric,组 varchar(10),值 varchar(10));
insert into @源(id,组,值) values(1,1,'a')
insert into @源(id,组,值) values(2,1,'b')
insert into @源(id,组,值) values(3,2,'c')
insert into @源(id,组,值) values(4,2,'d')
insert into @源(id,组,值) values(5,2,'e')
insert into @源(id,组,值) values(6,3,'f')
select * from @源
取 各"组"中前两条记录要求:
1.单SQL解决,别用游标.游标偶会...下面是游标:
declare @组 varchar(10);
declare @rtn table(id numeric,组 varchar(10),值 varchar(10));
declare cur cursor for select distinct 组 from @源;
OPEN cur;
FETCH NEXT FROM cur INTO @组;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @rtn select top 2 id,组,值 from @源 where 组=@组
FETCH NEXT FROM cur INTO @组;
END
CLOSE cur
DEALLOCATE cur
select * from @rtn
where id in (select top 2 id from @源
where 组=a.组 order by id)
from @源 a
where id in (select top 2 id from @源 where 组 = a.组)
insert into @源(id,组,值) values(1,1,'a')
insert into @源(id,组,值) values(2,1,'b')
insert into @源(id,组,值) values(3,2,'c')
insert into @源(id,组,值) values(4,2,'d')
insert into @源(id,组,值) values(5,2,'e')
insert into @源(id,组,值) values(6,3,'f')
select a.* from @源 a where id in(select top 2 id from @源 where 组=a.组)/*
id 组 值
-------------------- ---------- ----------
1 1 a
2 1 b
3 2 c
4 2 d
6 3 f
*/
Select * From @源 A Where Not Exists (Select 1 From @源 Where 组=A.组 And ID<A.ID Having Count(*)>1)
--方法三:
Select * From @源 A Where (Select Count(*) From @源 Where 组=A.组 And ID<A.ID)<2
from table1
where (select count(1)
from table1 A
where A.f1=table1.f1
and A.id>=table1.id) <=2
谢谢大家..来者有分..