我有两张桌子:
·表1:Books
·表2:Authors
我有以下查询:(此查询返回所有书名和出版日期的作者)
SELECT Authors.AuthorName, Books.BookTitle, Books.DatePublished, Books.Author
FROM Authors LEFT OUTER JOIN Books ON Authors.AuthorID = Books.Author问题:!!
我想归还所有的作者(都有书籍出版)。和那些没有出版书籍的人。
条件(有书的作者)。只返回最新出版的书)
我可以通过以下查询获得最新出版的书:
SELECT a.*
FROM [Books] a left outer join Books b on a.Author = b.author and
a.DatePublished < b.DatePublished where b.ISDN is null问题:我如何结合这两个查询返回所有作者+作者与最新出版的书?
发布于 2013-10-11 07:44:13
请尝试:
SELECT DISTINCT
AuthorName,
BookTitle,
DatePublished,
Author
FROM(
SELECT
Authors.AuthorName,
Books.BookTitle,
Books.DatePublished,
Books.Author,
ROW_NUMBER() over (partition by Authors.AuthorID order by DatePublished desc) Rnum
FROM Authors LEFT OUTER JOIN Books ON Authors.AuthorID = Books.Author
)x WHERE Rnum=1https://stackoverflow.com/questions/19312686
复制相似问题