如何获取子查询的结果以与另一个表联接?

Question

. 试图将借阅者、书籍到作者的结果链接在一起。

期望的结果：

AUTHORID         AUTHORFIRSTNAME      AUTHORLASTNAME
1                     JIM                   SPARKS
2                     JAMES                 ALLEN
3                     MARCUS                RASHFORD
20                    PAUL                  POGBA
22                    THIERRY               HENRY

但是不确定如何链接返回的顶级作者ID以检索作者名字和姓氏，但我没有在子查询中提到作者表

Answer 1

您可以将这三个表与rank()分析函数按子查询中count的降序连接起来，然后在主查询中取小于等于 5 ：

SELECT authorid, authorfirstname, authorlastname
FROM
(
SELECT a.authorid, a.authorfirstname, a.authorlastname, 
rank() over (order by count(*) desc)  as rnk
FROM AUTHOR a
LEFT JOIN BOOK bk ON a.authorid = bk.authorid
LEFT JOIN BORROWER br ON br.bookid = bk.bookid
WHERE br.borrowdate between date'2017-01-01' and date'2017-12-31'
GROUP BY a.authorid, a.authorfirstname, a.authorlastname
)
WHERE rnk <= 5
ORDER BY rnk

如果您使用的是数据库版本 12c+，则更容易获取它们：

SELECT a.authorid, a.authorfirstname, a.authorlastname, 
rank() over (order by count(*) desc)  as rnk
FROM AUTHOR a
LEFT JOIN BOOK bk ON a.authorid = bk.authorid
LEFT JOIN BORROWER br ON br.bookid = bk.bookid
WHERE br.borrowdate between date'2017-01-01' and date'2017-12-31'
GROUP BY a.authorid, a.authorfirstname, a.authorlastname
ORDER BY rnk 
FETCH FIRST 5 ROWS WITH TIES

我使用br.borrowdate between date'2017-01-01' and date'2017-12-31'而不是to_char(br.borrowdate) like '%2017'来使列borrowdate(如果有的话)上的索引受益。

上面的那些查询返回带有领带的行，例如，如果多行与第 5 行的值匹配，它们带来的行数超过 5 行。

不要将伪列用于排名目的rownum因为它的值是在排序之前计算的，可能会产生错误的结果。

Answer 2

所以，如果我没看错，你想做这样的事情：

select authorid, authorfirstname, authorlastname 
from 
(select a.authorid, a.authorfirstname, a.authorlastname 
from author a, borrower b, book c 
where a.authorid = c.authorid and c.bookid = b.bookid
and b.borrowdate like '%2017' 
group by c.bookauthor 
order by count(*) desc) xx 
where rownum <=5