所以我有一个问题,那就是找到每个作者写的最新的书。这是我正在使用的表:

这是预期的输出

到目前为止,我得到的结论是:
select unique fname || ' ' || lname "Author", max(pubdate) "Date
Pub"
from books join bookauthor using (isbn)
join author using (authorid)
group by fname, lname
order by 2,1;这就给出了输出
Author Date Pub
--------------------- ---------
JUAN ADAMS 04-JUN-03
TINA PETERSON 04-JUN-03
JACK BAKER 17-JUL-04
OSCAR FIELDS 17-JUL-04
LISA PORTER 21-JAN-05
WILLIAM WHITE 21-JAN-05
LISA WHITE 01-MAY-05
ROBERT ROBINSON 08-NOV-05
JAMES AUSTIN 31-DEC-05
JANICE JONES 01-MAR-06
TAMARA KZOCHSKY 18-MAR-06
SAM SMITH 11-NOV-06问题是,当我将标题添加到我的查询中时,所有的标题都会出现,而不仅仅是最近发布的那些。
select unique fname || ' ' || lname "Author Name", title , pubdate
from books join bookauthor using (isbn)
join author using (authorid)
order by 1,2;
Author Name TITLE PUBDATE
--------------------- ------------------------------ ---------
JACK BAKER COOKING WITH MUSHROOMS 28-FEB-04
JACK BAKER PAINLESS CHILD-REARING 17-JUL-04
JAMES AUSTIN DATABASE IMPLEMENTATION 04-JUN-03
JAMES AUSTIN HOLY GRAIL OF ORACLE 31-DEC-05
JANICE JONES E-BUSINESS THE EASY WAY 01-MAR-06
JANICE JONES REVENGE OF MICKEY 14-DEC-05发布于 2019-02-18 07:28:57
尝试这样做:
select unique fname || ' ' || lname "Author Name", title , pubdate
from books b
where isbn in (
select ba.isbn from (
select ba.isbn from bookauthor ba
join author a using (authorid)
order by pubDate
LIMIT 1
)t
)
order by 1,2;发布于 2019-02-18 07:30:38
在Oracle8.0(或几乎任何版本的MySQL )中,使用ROW_NUMBER()可以很直接地实现这一点:
SELECT * FROM (
SELECT
CONCAT(a.fname, ' ', a.lname),
b.title,
b.pubdate,
ROW_NUMBER() OVER(PARTITION BY b.authorid ORDER BY b.pubdate DESC) rn
FROM
books b
JOIN bookauthor ba using (isbn)
JOIN author a using (authorid)
) x WHERE rn = 1在不支持窗口函数的关系型数据库管理系统(如MySQL 5.x)中,使用NOT EXISTS条件可能比聚合更有效:
SELECT
CONCAT(a.fname, ' ', a.lname),
b.title,
ROW_NUMBER() OVER(PARTITION BY authorid ORDER BY pubdate DESC) rn
FROM
books b
JOIN bookauthor ba using (isbn)
JOIN author a using (authorid)
WHERE NOT EXISTS (
SELECT 1
FROM books b1 JOIN bookauthor ba1 using (isbn)
WHERE ba1.authorid = b.authorid AND b1.pubdate > b.pubdate
)https://stackoverflow.com/questions/54738619
复制相似问题