如何在from子句中使用子查询解决此问题？

小编典典

如何在from子句中使用子查询解决此问题？

sql

显示特定作者的零售价最高的所有书籍的作者，书名，零售价和零售价。

我有下面的查询。我有点困惑如何在from子句中进行子查询。

select lname, fname, title, retail
from author natural join bookauthor
natural join books
where retail=(select max(retail)
                 from books);

以下是我正在使用的数据库中的数据

FNAME      LNAME      TITLE                              RETAIL
---------- ---------- ------------------------------ ----------
SAM        SMITH      BODYBUILD IN 10 MINUTES A DAY       30.95
LISA       PORTER     BODYBUILD IN 10 MINUTES A DAY       30.95
JANICE     JONES      REVENGE OF MICKEY                      22
TAMARA     KZOCHSKY   BUILDING A CAR WITH TOOTHPICKS      59.95
TINA       PETERSON   DATABASE IMPLEMENTATION             55.95
JUAN       ADAMS      DATABASE IMPLEMENTATION             55.95
JAMES      AUSTIN     DATABASE IMPLEMENTATION             55.95
JACK       BAKER      COOKING WITH MUSHROOMS              19.95
JAMES      AUSTIN     HOLY GRAIL OF ORACLE                75.95
LISA       WHITE      HANDCRANKED COMPUTERS                  25
WILLIAM    WHITE      HANDCRANKED COMPUTERS                  25
JANICE     JONES      E-BUSINESS THE EASY WAY              54.5
ROBERT     ROBINSON   PAINLESS CHILD-REARING              89.95
OSCAR      FIELDS     PAINLESS CHILD-REARING              89.95
JACK       BAKER      PAINLESS CHILD-REARING              89.95
SAM        SMITH      THE WOK WAY TO COOK                 28.75
ROBERT     ROBINSON   BIG BEAR AND LITTLE DOVE             8.95
SAM        SMITH      HOW TO GET FASTER PIZZA             29.95
WILLIAM    WHITE      HOW TO MANAGE THE MANAGER           31.95
LISA       WHITE      SHORTEST POEMS                      39.95

20 rows selected.

阅读 180

2021-05-16

共1个答案

小编典典

您可以使用此：

SELECT lname, fname, title, retail
FROM author a
INNER JOIN bookauthor ba
ON a.id = ba.author_id
INNER JOIN books b
ON b.id = ba.book_id
WHERE (ba.author_id, ba.retail) IN (
    SELECT ba1.author_id, MAX(b1.retail)
    FROM books b1
    INNER JOIN bookauthor ba1
    ON ON b1.id = ba1.book_id
    GROUP BY ba1.author_id
    );

不要使用NATURE JOIN。对于所有学习者和程序员来说，这都是不好的加入方式。

（然后将author_id，book_id更改为您特定表的列名）

另一种方式：

SELECT lname, fname, title, retail
FROM author a
INNER JOIN bookauthor ba
ON a.id = ba.author_id
INNER JOIN books b
ON b.id = ba.book_id
INNER JOIN(
    SELECT ba1.author_id, MAX(b1.retail) retail
    FROM books b1
    INNER JOIN bookauthor ba1
    ON ON b1.id = ba1.book_id
    GROUP BY ba1.author_id
    ) mr
 ON 
    ba.author_id = mr.author_id 
    AND ba.retail = mr.retail
 ;

2021-05-16