SQL -找出表中最高的5个数字

Question

我有一些表格，其中包含所有关于书籍的信息，如标题，页面，价格，版税等。我想使用这些表格来查找具有最高版税的5位作者。

下面是我的表格：

CREATE TABLE authors
  (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12)         ,
  address  VARCHAR(20)         ,
  city     VARCHAR(15)         ,
  state    CHAR(2)             ,
  zip      CHAR(5)             ,
  CONSTRAINT pk_authors PRIMARY KEY (au_id)
  )ENGINE = InnoDB;


CREATE TABLE royalties
  (
  title_id     CHAR(3)      NOT NULL,
  advance      DECIMAL(9,2)         ,
  royalty_rate DECIMAL(5,2)         ,
  CONSTRAINT pk_royalties PRIMARY KEY (title_id)
  )ENGINE = InnoDB;

到目前为止，我只能选择最高的版税。我尝试使用TOP (5)函数，但对我不起作用。我的代码如下。

SELECT au_fname, au_lname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM title_authors
WHERE title_id IN
(SELECT title_id
FROM royalties
WHERE royalty_rate = (SELECT MAX(royalty_rate) FROM royalties)))

我怎样才能找到版税最高的5位作者？

Answer 1

MySQL不支持TOP关键字。除MySQL之外的一些数据库(例如SQL Server、Teradata等)确实支持这种语法。

MySQL通过LIMIT关键字提供了类似的功能。

 SELECT t.foo
   FROM mytable t
 ORDER BY t.bar DESC
 LIMIT 5

就获得结果而言，我强烈建议您使用join操作：

  SELECT a.au_fname
       , a.au_lname
    FROM authors a
    JOIN title_authors t 
      ON t.au_id = a.au_id
    JOIN royalties r
      ON r.title_id = t.title_id
   ORDER BY r.royalty_rate DESC
   LIMIT 5 

请注意，如果一个作者有两个(或更多)具有最高royalty_rate的标题，则该作者将多次返回。

如果你想要五个不同的作者，找到每个作者的最大版税，并按此排序。

  SELECT a.au_fname
       , a.au_lname
    FROM authors a
    JOIN title_authors t 
      ON t.au_id = a.au_id
    JOIN royalties r
      ON r.title_id = t.title_id
   GROUP BY a.au_id
   ORDER BY MAX(r.royalty_rate) DESC
   LIMIT 5

如果五个以上的作者具有相同的最高royalty_rate，那么这些作者中的哪五个是不确定的。您可以向ORDER BY子句添加其他表达式，以使结果更具确定性。

   ORDER BY MAX(r.royalty_rate) DESC, a.id DESC

Answer 2

您应该学习使用连接和聚合：

SELECT a.au_fname, a.au_lname
FROM authors a JOIN
     title_authors ta
     USING (au_id) JOIN    
     royalties r
     USING (title_id)
ORDER BY royalty_rate DESC
LIMIT 5;

MySQL使用LIMIT来获取前五行。