MySQL -多个计数、最小、最大查询

Question

SELECT
`name`, count(`cid`) AS count, Min(`price-3`) AS min, Max(`price-3`) AS max
FROM ed_prices
WHERE type="M"
GROUP BY cid, type

这个查询给出了我想要的类型M。但是我有4种不同的类型，比如M，并且每个类型的结果都是必需的。而这个查询得到的结果是这样的：

name, count, min, max

我需要这样的结果：

name, countM, minM, maxM, countP, minP, maxP, countZ, minZ, maxZ ...

我如何才能做到这一点？

Answer 1

我知道这不完全是你想要的，但你能不能用这个：

SELECT `name`
      , type                     -- you'll want to include the type for each line item 
                                 -- to be able to identify which type the values refer to
      , count(`cid`) AS count
      , Min(`price-3`) AS min
      , Max(`price-3`) AS max          
FROM ed_prices
WHERE type IN ("M","P","Z","Q")  -- where M, P, Z, Q are the different product types
GROUP BY cid, type

您还可以尝试对此进行自连接，以将结果合并到一行中，以获得问题中所要求的结果，但除了少数产品之外，它的可扩展性较差。

SELECT `name`
      , countM
      , minM
      , maxM
      , countZ
      , minZ
      , maxZ
      , countP
      , minP
      , maxP
      , countQ
      , minQ
      , maxQ
  FROM 
       (SELECT `name`
              , count(`cid`) AS countM
              , Min(`price-3`) AS minM
              , Max(`price-3`) AS maxM
          FROM ed_prices
         WHERE type = "M"
         GROUP BY cid) MType
  INNER JOIN 
       (SELECT `name`
              , count(`cid`) AS countP
              , Min(`price-3`) AS minP
              , Max(`price-3`) AS maxP
          FROM ed_prices
         WHERE type = "P"
         GROUP BY cid) PType ON MType.`name` = PType.`name`
  INNER JOIN 
       (SELECT `name`
              , count(`cid`) AS countZ
              , Min(`price-3`) AS minZ
              , Max(`price-3`) AS maxZ
          FROM ed_prices
         WHERE type = "Z"
         GROUP BY cid) ZType ON MType.`name` = ZType.`name`
  INNER JOIN 
       (SELECT `name`
              , count(`cid`) AS countQ
              , Min(`price-3`) AS minQ
              , Max(`price-3`) AS maxQ
          FROM ed_prices
         WHERE type = "Q"
         GROUP BY cid) QType ON MType.`name` = QType.`name`