我有一张 table 如下 .
我想在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
我该怎么做