mysql - 选择列值大于另一列值的行

Question

我正在尝试获取比我的数据库中的特定作者拥有更多图书的所有作者的姓名。

架构:(LIVRO 表示 BOOK)

我有这个问题:

SELECT AUTORIA.CODAUTOR, AU.NOMEAUT, COUNT(AUTORIA.CODLIVRO) FROM AUTORIA
INNER JOIN AUTOR AS AU ON AU.CODIGO = AUTORIA.CODAUTOR
GROUP BY AUTORIA.CODAUTOR;

这就是返回:

+----------+------------------+-------------------------+
| CODAUTOR | NOMEAUT          | COUNT(AUTORIA.CODLIVRO) |
+----------+------------------+-------------------------+
|        1 | MACHADO DE ASSIS |                       7 |
|        2 | NAVATHE          |                       6 |
|        3 | ELMASRI          |                       7 |
+----------+------------------+-------------------------+

现在我需要使用列 COUNT(AUTORIA.CODLIVRO) 的值大于作者 NAVATHE 的该列值的行过滤此结果。

这就是我的尝试:

SELECT AUTORIA.CODAUTOR, AU.NOMEAUT, COUNT(AUTORIA.CODLIVRO) FROM AUTORIA
INNER JOIN AUTOR AS AU ON AU.CODIGO = AUTORIA.CODAUTOR
GROUP BY AUTORIA.CODAUTOR /* Group books by author code */
/* Get authors with more books than NAVATHE */
HAVING COUNT(AUTORIA.CODLIVRO) > COUNT( /* Count how many books NAVATHE has */
    SELECT AUTORIA.CODLIVRO FROM AUTORIA /* Obtain the codes of the books of NAVATHE */
    INNER JOIN AUTOR AS AU ON AU.CODIGO = AUTORIA.CODAUTOR AND AU.NOMEAUT = 'NAVATHE'
);

但是这个查询给了我一个错误。

Answer 1

嗯，获取 id 的一种方法是:

SELECT a.CODAUTOR
FROM AUTORIA a
GROUP BY a.CODAUTOR
HAVING COUNT(*) > (SELECT COUNT(*)
                   FROM AUTORIA a2 JOIN
                        AUTOR au
                        ON au.CODIGO = a2.CODAUTOR
                   WHERE au.nome = 'NAVATHE'
                  );

我会让您计算出JOIN(或IN 或EXISTS 子句)来获取作者姓名。