mysql max(avg()),在mysql查询中使用min,max和avg

我有一张桌子如下.

我想在Single Query中获得最低,最高和平均成本产品的product_id.

CREATE TABLE productlist(product_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

cost INT);

INSERT INTO productlist(cost)

VALUES('2450'),

('2200'),

('2580'),

('2405'),

('3500'),

('1500'),

('1800'),

('1520'),

('1740'),

('1940'),

('2940'),

('1250'),

('1290'),

('1390'),

('2900');

输出：

Min 12

Max 5

Avg 2093

我试过下面的一个,但它不起作用.

SELECT product_id, MIN(cost) as mincost

FROM productlist

GROUP BY product_id

ORDER BY mincost ASC

LIMIT 0,1

UNION

SELECT product_id, max(cost) as maxcost

FROM productlist

GROUP BY product_id

ORDER BY maxcost DESC

LIMIT 0,1

我该怎么做

解决方法:

select 'Min', product_id

from productlist

where cost = (select min(cost) from productlist)

UNION

select 'Max', product_id

from productlist

where cost = (select MAX(cost) from productlist)

UNION

select 'Avg', round(AVG(cost),0) as Avg

from productlist

标签：mysql,sql,mysqli

来源： https://codeday.me/bug/20190704/1372171.html