分组取前两条记录

主　　题：  一个看似简单却很有难度的问题 
作　　者：  zhangjinwei_52 (野人) 
等　　级：   
信 誉 值：  100 
所属社区：  MS-SQL Server 疑难问题 
问题点数：  100 
回复次数：  9 
发表时间：  2006-05-16 10:00:48 
  
 
  
表Suburl中有如下记录
MainID   SubUrl
1        1111111111
1        2222222222
1        3333333333
1        4444444444
..........
2        1111111111
2        2222222222
2        3333333333
2        4444444444
..........
n...................

如何取出MainID=1,2,3...n的前2条记录即：
1        1111111111
1        2222222222
2        1111111111
2        2222222222
。。。。。
n        1111111111
n        2222222222
 

 
  libin_ftsafe(子陌红尘) ( ) 信誉：105  2006-05-16 10:03:00  得分: 0 
 
 
   select
    a.*
from
    Suburl a
where
    a.SubUrl in(select top 2 SubUrl from SubUrl where MainID=a.MainID)
order by
    a.MainID,a.Suburl
 
 
Top 
 Haiwer(海阔天空) ( ) 信誉：138  2006-05-16 10:04:00  得分: 0 
 
 
   select * from Suburl a
where Suburl in (select top 2 Suburl from Suburl where MainId=a.MainId)
 
 
Top 
 libin_ftsafe(子陌红尘) ( ) 信誉：105  2006-05-16 10:05:00  得分: 0 
 
 
   declare @t table(MainID int,SubUrl varchar(20))
insert into @t select 1,'1111111111'
insert into @t select 1,'2222222222'
insert into @t select 1,'3333333333'
insert into @t select 1,'4444444444'
insert into @t select 2,'1111111111'
insert into @t select 2,'2222222222'
insert into @t select 2,'3333333333'
insert into @t select 2,'4444444444'    

select
    a.*
from
    @t a
where
    a.SubUrl in(select top 2 SubUrl from @t where MainID=a.MainID)
order by
    a.MainID,a.Suburl

/*
MainID      SubUrl              
----------- --------------------
1           1111111111
1           2222222222
2           1111111111
2           2222222222
*/
 
 
Top 
 paoluo(一天到晚游泳的鱼) ( ) 信誉：100  2006-05-16 10:05:00  得分: 0 
 
 
   Select * From Suburl A
Where Not Exists(Select 1 From Suburl Where MainID=A.MainID And SubUrl<A.SubUrl Having Count(*)>1)
 
 
Top 
 WangZWang(阿来) ( ) 信誉：100  2006-05-16 10:06:00  得分: 0 
 
 
   select * from tbl as a
where SubUrl=(selec top 2 SubUrl
   from tbl MainID=a.MainID order by SubUrl)
 
 
Top 
 paoluo(一天到晚游泳的鱼) ( ) 信誉：100  2006-05-16 10:06:00  得分: 0 
 
 
   or

Select * From Suburl A Where (Select Count(*) From Suburl Where MainID=A.MainID And SubUrl<A.SubUrl)<2
 
 
Top 
 paoluo(一天到晚游泳的鱼) ( ) 信誉：100  2006-05-16 10:08:00  得分: 0 
 
 
   --建立測試環境
Create Table Suburl
(MainID Int,
 SubUrl Varchar(20))
Insert Suburl Select 1,        '1111111111'
Union All Select 1,        '2222222222'
Union All Select 1,        '3333333333'
Union All Select 1,        '4444444444'
Union All Select 2,        '1111111111'
Union All Select 2,        '2222222222'
Union All Select 2,        '3333333333'
Union All Select 2,        '4444444444'
Union All Select 3,        '1111111111'
Union All Select 3,        '2222222222'
Union All Select 3,        '3333333333'
Union All Select 3,        '4444444444'
--測試
--方法一:
Select * From Suburl A Where Not Exists(Select 1 From Suburl Where MainID=A.MainID And SubUrl<A.SubUrl Having Count(*)>1)
--方法二:
Select * From Suburl A Where (Select Count(*) From Suburl Where MainID=A.MainID And SubUrl<A.SubUrl)<2
--刪除測試環境
Drop Table Suburl
--結果
/*
MainID SubUrl
1 1111111111
1 2222222222
2 1111111111
2 2222222222
3 1111111111
3 2222222222
*/
 
 
Top 
 WangZWang(阿来) ( ) 信誉：100  2006-05-16 10:11:00  得分: 0 
 
 
   select * from tbl as a
where SubUrl in (selec top 2 SubUrl
   from tbl MainID=a.MainID order by SubUrl)

 
 
Top 
 itblog(^ω^) ( ) 信誉：100  2006-05-16 10:13:00  得分: 0 
 
 
   只能学习了~
 
 
Top