如果值大于平均值，MySQL 显示数据

Question

我有一个查询，我想选择或显示书籍数量高于平均水平的作者。

到目前为止，我获取平均图书数量的查询如下:

SELECT AVG(c.quantity) AS 'Average'
FROM(
SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
FROM autores_has_libros AS al
GROUP BY al.autores_DNI
) AS c

如何才能只选择(显示)那些书籍数量高于平均水平的作者？

此查询列出了作者及其书籍数量:

SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
    FROM autores_has_libros AS al
    GROUP BY al.autores_DNI

我已经尝试过:

SELECT a.first_name 
FROM autores AS a
HAVING (
SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
    FROM autores_has_libros AS al
    GROUP BY al.autores_DNI
)
> (
SELECT AVG(c.quantity) AS 'Average'
    FROM(
    SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
    FROM autores_has_libros AS al
    GROUP BY al.autores_DNI
    ) AS c
)

但这行不通

Answer 1

HAVING 子句中的第一个子查询不仅返回当前作者的计数，还返回所有其他作者的计数。但用作值的 SELECT 只能返回一个值。

相反，您应该使用子查询进行连接。

SELECT a.first_name
FROM aotores AS a
JOIN (
    SELECT al.autores_DNI AS Author,COUNT(*) AS quantity
    FROM autores_has_libros AS al
    GROUP BY Author) AS al ON al.Author = a.DNI
JOIN (
    SELECT AVG(c.quantity) AS Average
    FROM (
        SELECT COUNT(*) AS quantity
        FROM autores_has_libros AS al
        GROUP BY al.autores_DNI) AS c
) AS av ON al.quantity > av.Average

顺便说一句，在 MySQL 中，您使用反引号而不是单引号引用标识符。单引号用于创建文字字符串。