获取前N个不同值的所有记录

I have a BookAuthor table like below (SQL Server 2008 R2 Ent):

我有一个像下面的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

I want to get all records for top 2 distinct BookID. So, expected output will be as below:

我想获得前2个不同BookID的所有记录。所以,预期产量如下:

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

I tried the below simplest query and it is only returning 2 rows.

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

Select top 2 * from BookAuthor order by BookID ASC

So, how can I proceed here ? Any help is appreciated.

那么,我该怎么办呢?任何帮助表示赞赏。

2 个解决方案

#1

You can use the following to get the TOP 2 DISTINCT BookIds:

您可以使用以下内容获取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

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

You stated that you want the Books with the id's 43, 71 returned because those are the top 2 book ids but data in a table is not inherently ordered. Unless you have another column that you can get the rows in that order, if you order by bookid ascending then you will return 43, 64.

您声明您希望返回ID为43,71的书籍,因为这些是前2个书籍ID,但表格中的数据并非本身有序。除非您有另一列,您可以按顺序获取行,如果您按bookid升序排序,那么您将返回43,44。

#2

What about below query:

下面的查询怎么样:

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

It's working fine for me

它对我来说很好

#1