SQL(Oracle 12c):如何从另一个选择语句(嵌套?/子查询?)中选择结果 [英] SQL (Oracle 12c): How to select results from another select statement (nesting?/sub--query?)

问题描述

我正在为我正在学习的SQL入门课处理家庭作业问题.本周的主题是摘要功能.问题出在这里:

I'm working on a homework problem for an intro SQL class that I'm taking. This week's topic is Summary Functions. Here is the problem:

通过出版商名称和类别确定书籍的平均零售价格.仅包括儿童和计算机"类别以及平均零售价大于50美元的组.

以下是相关表格的图片:

我编写了此查询，该查询按发布者名称和类别返回平均零售价，但无法弄清楚如何仅返回超过50美元的平均零售价.

I've written this query which returns the average retail price by publisher name and category, but cannot figure out how to return just the average retail that is over $50.

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
group by p.name, b.category;

上面的代码返回以下内容:

The above code returns the following:

发布商Reed-N-Rite的平均零售价低于$ 50-需要添加到查询中的内容，以便我可以将语句中的结果过滤为零售价大于$ 50的那些?

Publisher Reed-N-Rite has an average retail below $50 - what needs to be added to my query so that I can filter the results from my statement to just those with retail > $50 ?

推荐答案

在使用聚合函数(例如avg)时，必须在其他列上使用group by；在使用group by时，必须使用Have for set where聚合列上的子句. 为此，使用具有":

When you use aggregate function (like avg),you must use group by on other columns, And when you use group by ,you must use having for set where clause on aggregated column. Use Having for this:

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
group by p.name, b.category 
having avg(b.retail) > 50

这篇关于SQL(Oracle 12c):如何从另一个选择语句(嵌套?/子查询?)中选择结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！