基于平均值使用max和min来确定最低平均成本和最高平均成本时出错(mysql)

6jygbczu 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(330)

我已根据以下内容确定了平均科目成本：

FROM BOOK
JOIN
(SELECT BOOK_SUBJECT,
Avg(BOOK_COST) AS AVGCOST
FROM BOOK
GROUP BY BOOK_SUBJECT) AS SUB USING (BOOK_SUBJECT)
ORDER BY BOOK_TITLE;

但是，我现在需要显示一个主题内最低的平均图书成本和一个主题内最高的平均图书成本。我知道我需要使用最小值和最大值，但不确定的语法，现在我必须根据它的平均值。
这是我的尝试。。。。。。

SELECT BOOK_SUBJECT
FROM BOOK
WHERE BOOK_COST =
(SELECT MIN(AVGCOST)
FROM BOOK)
ORDER BY BOOK_SUBJECT;

SELECT BOOK_SUBJECT
FROM BOOK
WHERE BOOK_COST =
(SELECT MAX(AVGCOST)
FROM BOOK)
ORDER BY BOOK_SUBJECT;

这将创建一个sql错误：字段列表中的未知列“avgcost”。我该怎么办？我知道它在寻找一个属性，但是找不到它，那么需要在代码中添加什么，以便mysql计算平均值的最小值和最大值呢？非常感谢那些回应你的人，你的反馈非常好。

mysql Max Min average

来源：https://stackoverflow.com/questions/53634403/error-using-max-and-min-based-on-avg-to-determine-lowest-average-cost-and-highes

1条答案

按热度按时间

dkqlctbz1#

我认为你想要达到的是

SELECT *
FROM BOOK
JOIN
(
   SELECT BOOK_SUBJECT,
   AVG(BOOK_COST) AS AVGCOST,
   MIN(BOOK_COST) AS MINCOST,
   MAX(BOOK_COST) AS MAXCOST
   FROM BOOK
   GROUP BY BOOK_SUBJECT
) AS SUB USING (BOOK_SUBJECT)
ORDER BY BOOK_TITLE;

基于

BOOK_SUBJECT BOOK_TITLE BOOK_COST
------------ ---------- ---------------------
MATHS        Book 1     15.99
MATHS        Book 2     14.99
MATHS        Book 3     13.99
ENGLISH      Book A     10.99
ENGLISH      Book B     9.99

退货

BOOK_SUBJECT BOOK_TITLE BOOK_COST AVGCOST MINCOST MAXCOST
------------ ---------- --------- ------- ------- -------
MATHS        Book 1     15.99     14.99   13.99   15.99
MATHS        Book 2     14.99     14.99   13.99   15.99
MATHS        Book 3     13.99     14.99   13.99   15.99
ENGLISH      Book A     10.99     10.49   9.99    10.99
ENGLISH      Book B     9.99      10.49   9.99    10.99

仅提取最小/最大平均成本（四舍五入到小数点后2位）

SELECT 
  ROUND(MIN(MM.AVGCOST), 2) AS MINAVG, 
  ROUND(MAX(MM.AVGCOST), 2) AS MAXAVG
FROM
(
   SELECT BOOK_SUBJECT, 
   AVG(BOOK_COST) AS AVGCOST
   FROM BOOK
   GROUP BY BOOK_SUBJECT
) MM

会回来的

MINAVG    MAXAVG
------    ------
10.49     14.99

赞(0）回复(0）举报 2021-06-16

我来回答

基于平均值使用max和min来确定最低平均成本和最高平均成本时出错(mysql)

1条答案

相关问题

热门标签

最新问答