如果值大于平均值,MySQL 显示数据 [英] MySQL show data if value greater than average
问题描述
我有这个查询,我想选择或显示书籍数量高于平均水平的作者.
I have this query where I want to select or show authors whose number of books is above the average.
到目前为止,我得到的平均图书数量的查询如下:
So far, the query I got to get the average number of books is the following:
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
然后我怎样才能只选择(显示)那些书数高于平均水平的作者?
How can I then select (show) only those authors whose number of books is above the average?
此查询列出了作者及其书籍数量:
This query lists the authors with their number of books:
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
)
但这不起作用
推荐答案
HAVING
子句中的第一个子查询不仅返回当前作者的计数,还返回所有其他作者的计数.但是一个 SELECT
用作值只能返回一个值.
Your first subquery in the HAVING
clause isn't returning just the count of the current author, it's returning the counts of all others. But a SELECT
used as a value can only return one value.
相反,您应该加入子查询.
Instead, you should join with the subquery.
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 中,您使用反引号引用标识符,而不是单引号.单引号用于创建文字字符串.
BTW, in MySQL you quote identifiers using backticks, not single quotes. Single quotes are for creating literal strings.
这篇关于如果值大于平均值,MySQL 显示数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!