SQL 查询返回一系列 ID 中每个 ID 的前 N ​​行

Question

假设我有一个包含几亿行的表，看起来像这样:

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

我需要一个查询，该查询将返回成员 ID 范围内每个 memID 的前 N ​​行。例如，如果 N = 3 并且范围是 0-2，它应该返回

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  

我考虑了几种方法，首先创建一个巨大的

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

在代码中查询。由于可能显而易见的原因，这并不是一个真正现实的选择。

第二种方法是创建一个临时表并遍历 memID 的范围，将每个 memID 的 TOP 3 插入该临时表。

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

    SET @MemID = @MemID + 1
    END

这行得通，但我想知道是否有我缺少的更优雅的单一查询解决方案。

Cadaeic 给了我一个无需修改就可以工作的答案，但感谢所有建议分析的人，看来我需要认真阅读一些内容。

Answer 1

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

结果:

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
)

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'