一个看似简单却很有难度的问题

[zhangjinwei_52]

表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

[子陌红尘]

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

[子陌红尘]

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
*/

[昵称被占用了]

select * from Suburl a
where Suburl in (select top 2 Suburl from Suburl where MainId=a.MainId)

[子陌红尘]

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
*/

[paoluo]

Select * From Suburl A
Where Not Exists(Select 1 From Suburl Where MainID=A.MainID And SubUrl<A.SubUrl Having Count(*)>1)

[WangZWang]

select * from tbl as a
where SubUrl=(selec top 2 SubUrl
from tbl MainID=a.MainID order by SubUrl)

[paoluo]

or

Select * From Suburl A Where (Select Count(*) From Suburl Where MainID=A.MainID And SubUrl<A.SubUrl)<2

[paoluo]

--建立測試環境
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
*/

[WangZWang]

select * from tbl as a
where SubUrl in (selec top 2 SubUrl
from tbl MainID=a.MainID order by SubUrl)

[itblog]

只能学习了~