如何在 from 子句中使用子查询来解决这个问题? [英] How to solve this using a subquery in a from clause?

问题描述

显示特定作者零售价最高的所有图书的作者、书名、零售价和零售价.

Display author, title, retail and retail price of all books whose retail price is the highest for the specific author.

我有以下问题.我有点困惑如何在 from 子句中执行子查询.

I have the query below. I'm kinda confused how to do a subquery in a from clause.

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

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

Below is the data from the database that I'm using

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.

推荐答案

你可以使用这个:

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.对于所有学习者和程序员来说，这是一种糟糕的加入方式.

Do not use NATURE JOIN. This is bad way of join, for all learner and programmer.

(并将 author_id、book_id 更改为特定表的列名)

(And change author_id, book_id to column name of your specific table)

其他方式:

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
 ; 

这篇关于如何在 from 子句中使用子查询来解决这个问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！