声明@startID int，@endID int， rowsEach int 
 select @startID = 0，@endID = 2，@rowsEach = 3 


 select * 
从
（
 select memc，foo，bar，foobar，row_number（）over（partition by dense_rank order by dense_rank）[rank_row] 
从
（
选择memID，foo，bar，foobar，dense_rank （通过memID命令）[dense_rank] 
来自#test 
其中在@startID和@endID之间的memID 
）a 
）b 
其中rank_row   

结果：

  memID foo bar foobar rank_row 
 ----------- ---- ---- ------ ---------- ---------- 
 1 blah blah blah 1 
 1 blah blah blah 2 
 1 blah blah blah 3 
 2 blah blah blah 1 
 2 blah blah blah 2 
 2 blah blah blah 3 
 

这里是设置代码如果你想在本地测试：

  create table #test 
（
 memID int not null 
，foo char（4）not null 
，bar char（4）not null 
，foobar char（4）not null 
）
进入#test（memID，foo，bar，foobar）
 select 1，'blah'，'blah'，'blah'
 union all 
 select 1，'blah'，'blah' ，'blah'
 union all 
 select 1，'blah'，'blah'，'blah'
 union all 
 select 1，'blah'，'blah' blah'
 union all 
 select 1，'blah'，'blah'，'blah'
 union all 
 select 1，'blah'，'blah'，'blah' 
 union all 
 select 1，'blah'，'blah'，'blah'
 union all 
 select 2，'blah'，'blah'，'blah'
 union all 
 select 2，'blah'，'blah'，'blah'
 union all 
 select 2，'blah'，'blah'，'blah'
 union all 
 select 2，'blah'，'blah'，'blah'
 union all 
选择10001，'blah'，'blah'，'blah'
 union all 
 select 10001，'blah'，'blah'，'blah'
 union all 
选择10001，'blah'，'blah'，'blah'
 

Lets say I have a table containing several hundred million rows that looks something like this:

memID | foo  | bar  | foobar
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
.
.
.
10001 | blah | blah | blah
10001 | blah | blah | blah

I need a query that will return the top N rows for each memID in a range of member IDs. For example, if N = 3 and the range is 0-2 it should return

memID | foo  | bar  | foobar
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah  

I've considered a couple approaches, first creating a massive

SELECT TOP (3) *
FROM table
WHERE memID = 0
UNION ALL
SELECT TOP (3) *
FROM table
WHERE memID = 1
.
.
.

query in code. This isn't really a realistic option for probably obvious reasons.

The second approach is to create a temporary table and loop over the range of memIDs inserting the TOP 3 for each memID into that temporary table.

WHILE @MemID < 10000 AND @MemID > 0
  BEGIN
    INSERT INTO tmp_Table
    SELECT TOP (3) *
     FROM table
     WHERE memID = @MemID

    SET @MemID = @MemID + 1
    END

This works, but I'm wondering if there is a more elegant single query solution that I'm missing.

Cadaeic gave me an answer that works without tinkering, but thank you to everyone that suggested analytics, it looks like I have some serious reading to do.

declare @startID int, @endID int, @rowsEach int
select @startID = 0, @endID = 2, @rowsEach = 3


select *
from
(
    select memID, foo, bar, foobar, row_number() over (partition by dense_rank order by dense_rank) [rank_row]
    from
    (
        select memID, foo, bar, foobar, dense_rank() over (order by memID) [dense_rank]
        from #test
        where memID between @startID and @endID
    ) a
) b
where rank_row <= @rowsEach

The result:

memID       foo  bar  foobar rank_row
----------- ---- ---- ------ --------------------
1           blah blah blah   1
1           blah blah blah   2
1           blah blah blah   3
2           blah blah blah   1
2           blah blah blah   2
2           blah blah blah   3

And here's the set-up code if you'd like to test locally:

create table #test
(
      memID     int not null
    , foo       char(4) not null
    , bar       char(4) not null
    , foobar    char(4) not null
)

insert into #test (memID, foo, bar, foobar)
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 10001, 'blah', 'blah', 'blah'
union all
select 10001, 'blah', 'blah', 'blah'
union all
select 10001, 'blah', 'blah', 'blah'