|
显示特定作者的零售价最高的所有书籍的作者,书名,零售价和零售价。2 {0 o9 `( [: o* Z' n" d
我有下面的查询。我有点困惑如何在from子句中进行子查询。
% _0 ]/ l8 G6 h0 ?* P; c& }2 Kselect lname, fname, title, retail- t9 T, D' l$ N2 C5 f
from author natural join bookauthor
( F4 w- h! \" [natural join books
8 i# j+ Q* J3 i* Owhere retail=(select max(retail)- l1 h7 a: r, _4 Q+ n4 X
from books);8 u/ _- ]1 R0 @* V; E2 ^4 x
以下是我正在使用的数据库中的数据
{$ r# S1 a8 iFNAME LNAME TITLE RETAIL R ~% c# h% {5 [5 ^4 w: K( a- a2 O
---------- ---------- ------------------------------ ----------
, n5 g; A( R R3 W3 y# uSAM SMITH BODYBUILD IN 10 MINUTES A DAY 30.95
- N+ u- A4 Z/ o$ hLISA PORTER BODYBUILD IN 10 MINUTES A DAY 30.95
# o' T9 I5 c( O$ F5 f% Z3 aJANICE JONES REVENGE OF MICKEY 22
( Y3 Y* n% t- u2 m1 x( G8 M0 uTAMARA KZOCHSKY BUILDING A CAR WITH TOOTHPICKS 59.95
J; o4 A! ?" h% Y8 nTINA PETERSON DATABASE IMPLEMENTATION 55.95
# \& Z" ~% A5 n% T! i; H4 Z/ \JUAN ADAMS DATABASE IMPLEMENTATION 55.95
7 n! |6 P! \5 [$ ~1 fJAMES AUSTIN DATABASE IMPLEMENTATION 55.95
: x4 t' p" e9 {6 \7 W; QJACK BAKER COOKING WITH MUSHROOMS 19.95/ [6 v5 B' Z+ ~: t
JAMES AUSTIN HOLY GRAIL OF ORACLE 75.95+ a; I+ D# ~, y) p+ Y
LISA WHITE HANDCRANKED COMPUTERS 25
% v, [/ M% T& f, N1 E; Z1 NWILLIAM WHITE HANDCRANKED COMPUTERS 25, S9 |' C% V% I( ?, L6 U1 J
JANICE JONES E-BUSINESS THE EASY WAY 54.5
' R* H( `% x3 I4 ^- _( e9 eROBERT ROBINSON PAINLESS CHILD-REARING 89.95% d5 U+ F0 V' d) o
OSCAR FIELDS PAINLESS CHILD-REARING 89.95
( @) f( R0 `; N, m% Z2 k" QJACK BAKER PAINLESS CHILD-REARING 89.95" V! K b9 I! _, ?! {7 t1 G+ y
SAM SMITH THE WOK WAY TO COOK 28.75% I5 P% i% c2 F
ROBERT ROBINSON BIG BEAR AND LITTLE DOVE 8.958 Y ?1 Y8 R4 T
SAM SMITH HOW TO GET FASTER PIZZA 29.95% U3 g* \) O( G0 l) {# v
WILLIAM WHITE HOW TO MANAGE THE MANAGER 31.95# R% a- x1 g* X% i3 \
LISA WHITE SHORTEST POEMS 39.95" I+ Q' H7 e- n0 p. A
20 rows selected.
5 R3 X6 X! P4 m0 Z! b3 n* R
- W+ N9 i8 J0 I$ n1 V解决方案:
9 S$ M" q- {: O; Q7 @& Q8 t
9 d0 w9 z, g* N: N/ T3 J
# E. a; {+ e: h7 _2 V
4 h Q" }) ]2 i/ Y; S) r 您可以使用此:9 r3 i: O6 f# n6 H# O' K! P# A1 U
SELECT lname, fname, title, retail
: F* d1 q$ m0 [/ ~4 d; ]! _FROM author a
( T; C2 u1 a4 V& R% s3 R& QINNER JOIN bookauthor ba: c) Z* I: I" b% `: r; i
ON a.id = ba.author_id3 m& y% @& n( U4 ] s* e" L
INNER JOIN books b
- ?$ S/ }! d$ @7 p5 P2 x8 x6 ZON b.id = ba.book_id# Y: ?3 D" J; G' @
WHERE (ba.author_id, ba.retail) IN (. d/ g' k& `1 A0 z
SELECT ba1.author_id, MAX(b1.retail)' G9 B. |6 t$ t7 ]
FROM books b1
- l7 L4 |2 U! u" O6 \ INNER JOIN bookauthor ba1* ^* e5 Y# @: ]# ]# O0 B
ON ON b1.id = ba1.book_id' r" a; E3 w2 k. K
GROUP BY ba1.author_id2 J/ X6 J( ?' P4 r, a3 q
);: I6 f6 k2 m; h8 s4 t1 J, F. Y! r
不要使用NATURE JOIN。对于所有学习者和程序员来说,这都是不好的加入方式。1 f: @7 j, M3 v, C
(然后将author_id,book_id更改为您特定表的列名)
0 n/ b# C5 ? g另一种方式:
% J' s" `& f S/ k& ESELECT lname, fname, title, retail b# Z: V- \" x
FROM author a! T! A" \" |: j C! o1 v* F8 [* B
INNER JOIN bookauthor ba
% b d0 J; d) [+ M4 c( vON a.id = ba.author_id- ?! K. _/ a' l' a, c
INNER JOIN books b
" j5 `7 Q6 q ^" [" W+ t# R* a+ Q- VON b.id = ba.book_id# q1 P, L% H, U' R
INNER JOIN(
1 \ T; r; G8 O" F SELECT ba1.author_id, MAX(b1.retail) retail
8 [7 D8 I1 S& V4 E* W& t' f FROM books b1
, p* @2 O, ?5 d4 ^9 ~ INNER JOIN bookauthor ba1' T- G9 N: b% b' w! Q* T
ON ON b1.id = ba1.book_id
) i; b$ [9 [' { GROUP BY ba1.author_id
- A$ g) G# e4 ^; ^! V" z ) mr1 j# Y* W, V0 D; j' e
ON
4 x5 j+ G7 V* E+ e2 u- D* v* D ba.author_id = mr.author_id
4 z: J; x+ ]; P% }- T AND ba.retail = mr.retail) X V- h: C2 S% p. e# y
; |
|