首页 文章

在mysql查询中使用min，max和avg

提问于
浏览 次
4

我有一张 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

我该怎么做

mysql sql mysqli

5 回答

  • 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
    回复于
  • 1
    select product_id, cost
from productlist where cost = (SELECT max(cost)from productlist)
union
select product_id, cost
from productlist where cost = (SELECT min(cost)from productlist)
union
select product_id, cost
from productlist where cost = (SELECT x.cost from productlist x, productlist y
GROUP BY x.cost
HAVING SUM(SIGN(1-SIGN(y.cost-x.cost))) = (COUNT(*)+1)/2)

    这使用了中位数，在每种情况下都返回产品ID

    回复于
  • 0

    这确切地回答了您的问题，但应该注意，需要3次表扫描才能找到这些数据 . 此外，问题中的示例表明，平均值从2093.67降至2093 . 用round替换它可能更好 .

    SQL Fiddle

    SELECT concat('Min ', product_id) 
  FROM productlist
 WHERE cost = (SELECT min(cost) from productlist)
UNION ALL
SELECT concat('Max ', product_id) 
  FROM productlist
 WHERE cost = (SELECT max(cost) from productlist)
UNION ALL
SELECT concat('Avg ', truncate(avg(cost), 0)) 
  FROM productlist
    回复于
  • 0

    这是为了选择所有产品

    SELECT 
          max(cost), MIN(cost), AVG(cost)
    FROM 
          productlist
    GROUP BY
          product_id

    这里不完全需要GROUP BY . 但似乎你是初学者，谷歌搜索将帮助你 .

    对于你的问题，试试这个 .

    SELECT 
          (select CONCAT(product_id, '-', cost) from  productlist group by product_id order by cost DESC limit 1) as MAX,
          (select CONCAT(product_id, '-', cost) from  productlist group by product_id order by cost ASC limit 1) as MIN,
          (select avg(cost) from  productlist) as AVG
FROM 
           productlist limit 1
    回复于
  • 0

    你想要的输出不是来自你写的查询，你需要尝试这个以获得所需的输出

    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', floor(AVG(cost)) as Avg
from productlist
    回复于

相关问题