获取Top N Distinct值的所有记录

Question

我有一个BookAuthor表如下(SQL Server 2008 R2 Ent):

BookID  AuthorID
------  --------
43      676
43      76
43      354
71      89
71      76
99      71
64      50
64      39
64      354

我想获得前2个不同BookID的所有记录。因此，预期输出将如下所示:

    BookID  AuthorID
    ------  --------
    43      676
    43      76
    43      354
    71      89
    71      76

我尝试了下面最简单的查询，它只返回2行。

Select top 2 * from BookAuthor order by BookID ASC

那么，我该怎么做呢?

Answer 1

您可以使用以下命令获取TOP 2 DISTINCT BookIds:

select t1.bookid, t1.authorid
from BookAuthor t1
inner join
(
  select distinct top 2 bookid
  from BookAuthor
  order by bookid
) t2
  on t1.bookid = t2.bookid

参见SQL Fiddle with Demo

您声明希望返回id为43,71的Books，因为它们是前2个book id，但表中的数据本身并不是有序的。除非你有另一列，你可以按这个顺序得到行，如果你按图书升序排序，那么你将返回43,64

Answer 2

下面的查询:

Select * from BookAuthor where BookID in(
select Distinct top 2 BookID from BookAuthor order by BookID asc)