sql - SQL 查询中不同的关键字用法

Question

我有一个表叫手稿

BOOKID  AUTHORID BOOKNAME       GENRE   NOOFCOPIES  AMOUNT
B105    A102     Diagnosis      Journal 500         10000
B102    A102     The Essence... Fiction 110         11000
B106    A102     Clinical Sci.  Educat. 125         18750
B104    A103     Mind Games     Fiction 200         20000
B100    A102     Sublime        Fiction 100         10000
B101    A105     C Programming  Educati 125         18750
B103    A101     Scribe         Journal 500         10000
B107    A101     Clinical Chem. Educat. 125         54646
B108    A103     Clinical Chem. Educat. 125         54646  

如何获取已支付最高金额的 AuthorId？如果我写

select distinct a.AuthorId,m.amount 
from Author a,manuscripts m 
where m.amount in (select max(amount) 
                   from manuscripts m 
                   where a.AuthorId=m.AuthorId)

然后我能够得到正确的输出，但是如果我添加 bookname 那么它不会得到正确的结果..

Answer 1

因为您没有提到您正在使用的 RDBMS，下面的查询几乎可以使用 RDBS，(不是所有)

SELECT  a.*
FROM    tablename a
        INNER JOIN
        (
            SELECT  AuthorID, MAX(Amount) max_amount
            FROM    tableName
            GROUP   BY AuthorID
        ) b ON  a.AuthorID = b.AuthorID AND
                a.Amount = b.max_amount

为了更快的性能，在 AuthorID, Amount 上添加一个复合列 INDEX